package com.roc.service.impl;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.roc.bean.AgentSubsidyBean;
import com.roc.bean.BlackListBean;
import com.roc.bean.ChangeAgentCountBean;
import com.roc.bean.ChangeCheckCountBean;
import com.roc.bean.CommNumberStatBean;
import com.roc.bean.ErrorCountBean;
import com.roc.bean.FreezeBean;
import com.roc.bean.FreezeListBean;
import com.roc.bean.MerDetailForysBean;
import com.roc.bean.MerchantsChangeRecordBean;
import com.roc.bean.MerinfoSearchBean;
import com.roc.bean.MerinfoTermBean;
import com.roc.bean.OrgSettlementBean;
import com.roc.bean.ReviseMerinfoBean;
import com.roc.bean.RiskYsbEstrnInfoBean;
import com.roc.bean.TblMgmRiskBackLog;
import com.roc.bean.TermActForYsbBean;
import com.roc.bean.TermBean;
import com.roc.dao.IBaseDao;
import com.roc.dao.ISQLBaseDao;
import com.roc.dao.IorgDao;
import com.roc.pojo.AccountingStatistics;
import com.roc.pojo.AuditingItem;
import com.roc.pojo.BlackList;
import com.roc.pojo.CalcuBean;
import com.roc.pojo.CompTransCount;
import com.roc.pojo.Cycleinfo;
import com.roc.pojo.DayStastics;
import com.roc.pojo.DuizhangBean;
import com.roc.pojo.Estrnlog;
import com.roc.pojo.MerchantReport;
import com.roc.pojo.MerinfoChange;
import com.roc.pojo.Negotiation;
import com.roc.pojo.NoEstrnMerchantInfo;
import com.roc.pojo.Operators;
import com.roc.pojo.RiskSurveyOrder;
import com.roc.pojo.SettingBean;
import com.roc.pojo.TblActSum;
import com.roc.pojo.TblClearTxn;
import com.roc.pojo.TblMchtBankAccount;
import com.roc.pojo.TblMchtBizFee;
import com.roc.pojo.TblMchtInf;
import com.roc.pojo.TblSettleItem;
import com.roc.pojo.TblTerActivateStatistics;
import com.roc.pojo.TblTermBrandStatistics;
import com.roc.pojo.TblTermInf;
import com.roc.pojo.TblTermRiskCfg;
import com.roc.pojo.TblTfrHisTrnLog;
import com.roc.pojo.TblTfrTrnLog;
import com.roc.pojo.TblTransJnlsSettle;
import com.roc.pojo.UnPay;
import com.roc.pojo.UnPayStatement;
import com.roc.pojo.WareHouseSNStatisticsInfo;
import com.roc.pojo.WindControllSta;
import com.roc.pojo.susesMerinfo;
import com.roc.service.IApplyMoneyService;
import com.roc.service.IExcelService;
import com.roc.service.ServiceException;
import com.roc.service.freeze.IFreezeService;
import com.roc.util.Constance;
import com.roc.util.DateUtil;
import com.roc.util.ExcelUtil;
import com.roc.util.Utils;
import com.roc.util.constant.BcTranType;
import com.roc.util.constant.ConfigProperties;
import com.roc.util.constant.Constant;
import com.roc.util.constant.Role;
import com.roc.web.action.merchangerecord.MerchantsChangeRecordSearchCondition;
import com.roc.web.action.reviseMerinfo.ReviseMerinfoSearchCondition;
import com.roc.web.action.windcontroll.OrderProcessSearchCondition;

/**
 * @author zbl
 * @date 2014-08-28
 * */
@Service
@Transactional
@SuppressWarnings("all")
public class ExcelServiceImpl implements IExcelService {
	static Logger logger = Logger.getLogger(ExcelServiceImpl.class);
	@Autowired
	IBaseDao baseDao;
	@Autowired
	ISQLBaseDao sqlBaseDao;
	@Autowired
	IorgDao orgDao;
	@Autowired
	IFreezeService freezeService; 
	@Autowired
	IApplyMoneyService applyService;
	/**
	 * @param 历史交易excel导出
	 * 
	 * 
	 * */
	@Override
	public HSSFWorkbook hisTransQueryExcel(String startDate, String endDate,
			String mid) throws ServiceException {

		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			// 查询交易明细(显示正常的交易)
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT e.ACCOUNT,t.tran_name,e.amount,s.mer_name,e.etstermid,e.etstime,e.etsdate,e.ETSTRACE,e.RESPCODE,e.REVERSAL,e.CARDBANKID,e.period,e.termtrace,s.mid  ");
			sb.append(" FROM ESTRNHISLOG e ");
			sb.append(" INNER JOIN bc_tran_type t ");
			sb.append(" ON e.etstrancode = t.tran_code ");
			sb.append(" INNER JOIN ESMIDINFO s ");
			sb.append(" ON s.mid = e.merno  ");
			sb.append(" WHERE e.REVERSAL='0' ");
			sb.append(" and e.RESPCODE='00' ");
			sb.append(" and e.etstrancode='")
					.append(BcTranType.BC_TRAN_TYPE_4.getCode()).append("' ");
			sb.append(" ");
			if (mid != null) {
				sb.append(" and e.merno='").append(mid).append("' ");
			}
			sb.append(" and e.etsdate>='").append(startDate)
					.append("' and e.etsdate<='").append(endDate).append("'");
			List<Object> objList = sqlBaseDao.queryAll(sb.toString());
			if (objList == null || objList.size() < 1) {
				throw new ServiceException("根据条件查询无结果！");
			}
			HSSFSheet sheet = wb.createSheet("商户交易对账");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 16, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("商户历史交易"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = {
					"报表日期：",
					Constance.formateDate(),
					"",
					"结算日期",
					startDate.replace("-", "") + "至" + endDate.replace("-", ""),
					"", "", "", "", "", "" };
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);
			Object[] obj = (Object[]) objList.get(0);
			String[] coloumValue2 = { "商户名称：",
					Constance.Null2EmptyTrim(obj[3].toString()), "", "商户号码",
					Constance.Null2EmptyTrim(obj[13].toString()), "", "", "",
					"", "", "" };

			row = ExcelUtil.createRow(sheet, 2, coloumValue2, general_style,
					true);
			String[] coloumValue3 = { "银行卡号", "交易类型", "交易金额", "终端号码", "系统参考号",
					"交易返回码", "状态", "批次号", "流水号", "交易日期", "交易时间" };
			row = ExcelUtil.createRow(sheet, 3, coloumValue3, data_style, true);
			Double amt = 0.0;
			for (int i = 0; i < objList.size(); i++) {
				Object[] objs = (Object[]) objList.get(i);
				String[] col_i = {
						Utils.dealCardNo(Constance.Null2EmptyTrim(objs[0])),
						Constance.Null2EmptyTrim(objs[1]),
						Constance.isMoney(objs[2].toString().trim()),
						Constance.Null2EmptyTrim(objs[4]),
						Constance.Null2EmptyTrim(objs[7]),
						Utils.dealRespCodeNames(Constance
								.Null2EmptyTrim(objs[8])),
						ConfigProperties.getRevasalTypeName(Constance
								.Null2EmptyTrim(objs[9])),
						Constance.Null2EmptyTrim(objs[11]),
						Constance.Null2EmptyTrim(objs[12]),
						Constance.Null2EmptyTrim(obj[6]),
						Constance.Null2EmptyTrim(obj[5]) };
				row = ExcelUtil.createRow(sheet, 4 + i, col_i, general_style,
						true);
				amt = amt
						+ Double.parseDouble(Constance.isMoney(objs[2]
								.toString().trim()));
			}
			String[] coloumValue8 = { "(总计)", "", Double.toString(amt), "", "",
					"", "", "", "", "", "" };
			row = ExcelUtil.createRow(sheet, objList.size() + 4, coloumValue8,
					general_style, true);

			String[] coloumValue4 = { "(终端交易汇总)", "", "", "", "", "", "", "",
					"", "", "" };
			row = ExcelUtil.createRow(sheet, objList.size() + 5, coloumValue4,
					general_style, true);

			String[] coloumValue5 = { "终端号码", "交易笔数", "交易金额", "", "", "", "",
					"", "", "", "" };
			row = ExcelUtil.createRow(sheet, objList.size() + 6, coloumValue5,
					data_style, true);
			int transCount = 0;

			Map<String, TermBean> mapList = new LinkedHashMap<String, TermBean>();
			String ter = null;
			// Object[] tmpObj= new Object[coloumValue2.length];
			for (int j = 0; j < objList.size(); j++) {
				Object[] objs = (Object[]) objList.get(j);

				if (mapList.containsKey(Constance.Null2EmptyTrim(objs[4]))) {
					TermBean tb = mapList
							.get(Constance.Null2EmptyTrim(objs[4]));
					// 交易笔数
					tb.setTransNums(tb.getTransNums() + 1);
					// 交易金额
					tb.setTransAmt(tb.getTransAmt()
							+ Double.parseDouble(Constance.isMoney(objs[2]
									.toString().trim())));

					mapList.put(tb.getTid(), tb);
					transCount++;

				} else {
					TermBean tb = new TermBean();
					tb.setTid(Constance.Null2EmptyTrim(objs[4]));// 终端号
					tb.setTransAmt(Double.parseDouble(Constance.isMoney(objs[2]
							.toString().trim())));// 金额
					tb.setTransNums(1);// 交易次数
					mapList.put(tb.getTid(), tb);
					transCount++;

				}
			}
			int a = 0;
			for (String str : mapList.keySet()) {
				String[] col_i = { mapList.get(str).getTid(),
						Integer.toString(mapList.get(str).getTransNums()),
						Double.toString(mapList.get(str).getTransAmt()), "",
						"", "", "", "", "", "", "" };
				row = ExcelUtil.createRow(sheet, objList.size() + 7, col_i,
						general_style, true);

			}

		} catch (Exception e) {
			logger.error("########导出历史交易Excel明细失败######", e);
			throw new ServiceException("导出历史交易Excel明细失败！");
		}
		return wb;
	}

	@Override
	public HSSFWorkbook settingTransQueryExcel(List<CalcuBean> calcuList,
			String startDate, String endDate) throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("清算对账");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("清算明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = {
					"报表日期：",
					Constance.formateDate(),
					"",
					"清算日期",
					startDate.replace("-", "") + "至" + endDate.replace("-", ""),
					"", "", "", "", "", "", "", "", "", "" };
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "商户号", "商户名称", "终端号", "系统跟踪号", "系统流水号",
					"交易流水号", "卡号", "交易日期", "交易时间", "交易状态", "交易金额", "商户手续费用",
					"总结算金额", "清算状态", "批次号", "清算日期" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			Double amt = 0.0;// 总金额
			Double jyAmt = 0.0;// 交易总金额
			Double feeAmt = 0.0;// 总手续费
			for (int i = 0; i < calcuList.size(); i++) {
				CalcuBean objs = calcuList.get(i);
				objs.setRespcode(respCodes.get(Constance.Null2EmptyTrim(objs
						.getRespcode())));
				if (Constance.Null2EmptyTrim(objs.getSettflag()).equals("Y")) {
					objs.setSettflag("已清算");
				} else {
					objs.setSettflag("未清算");
				}

				String[] col_i = {
						Constance.Null2EmptyTrim(objs.getMerno()).substring(
								3,
								Constance.Null2EmptyTrim(objs.getMerno())
										.length()),
						Constance.Null2EmptyTrim(objs.getRealname()),
						Constance.Null2EmptyTrim(objs.getEtstermid()),
						Constance.Null2EmptyTrim(objs.getEtstrace()),
						Constance.Null2EmptyTrim(objs.getSystemno()),
						Constance.Null2EmptyTrim(objs.getTermtrace()),
						Constance.Null2EmptyTrim(objs.getAccount()),
						Constance.Null2EmptyTrim(objs.getTermdate()),
						Constance.Null2EmptyTrim(objs.getTermtime()),
						Constance.Null2EmptyTrim(objs.getRespcode()),
						Constance.null2Amt(objs.getAmount()),
						Constance.null2Amt(objs.getMerfee()),
						Constance.null2Amt(objs.getCalcuamount()),
						Constance.Null2EmptyTrim(objs.getSettflag()),
						Constance.Null2EmptyTrim(objs.getSettbatch()),
						Constance.Null2EmptyTrim(objs.getCalcudate()) };
				jyAmt = Double
						.parseDouble(Constance.null2Amt(objs.getAmount()))
						+ jyAmt;
				feeAmt = Double
						.parseDouble(Constance.null2Amt(objs.getMerfee()))
						+ feeAmt;
				amt = Double.parseDouble(Constance.null2Amt(objs
						.getCalcuamount())) + amt;
				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}
			/*
			 * row=sheet.createRow(calcuList.size()+3); for(int i=0;i<15;i++){
			 * row.createCell(i); }
			 */
			// sheet.addMergedRegion(new
			// CellRangeAddress(calcuList.size()+3,calcuList.size()+3,0,8));
			DecimalFormat df = new DecimalFormat("0.00");
			String[] coloumValue = { "汇总：", "", "", "", "", "", "", "", "", "",
					"", "", "", "", "" };
			row = ExcelUtil.createRow(sheet, calcuList.size() + 3, coloumValue,
					general_style, true);

			// String[]
			// coloumValue8={"交易总金额：",Double.toString(jyAmt).trim(),"交易总手续费：",Double.toString(feeAmt),"总结算金额：",Double.toString(amt)};
			String[] coloumValue8 = { "交易总金额：", df.format(jyAmt), "交易总手续费：",
					df.format(feeAmt), "总结算金额：", df.format(amt), "", "", "",
					"", "", "", "", "", "" };

			row = ExcelUtil.createRow(sheet, calcuList.size() + 4,
					coloumValue8, general_style, true);

		} catch (Exception e) {
			logger.error("########导出清算Excel明细失败######", e);
			throw new ServiceException("导出清算Excel明细失败！");
		}
		return wb;
	}

	@Override
	public HSSFWorkbook merinfoTransQueryExcel(
			List<MerinfoSearchBean> merinfoList, Operators op)
			throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("批量商户详情");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 16, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("商户详情"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "", "",
					"", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
					"", "", "", "" };
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "商户号", "商户名", "商户简称", "商户性质", "商户状态",
					"业务联系人", "法人电话", "法人姓名", "结算周期", "法人证件类型", "法人证件号码",
					"营业地址", "代理商", "营业起始时间", "营业结束时间", "mcc码", "开通时间", "结算类型",
					"结算扣率", "封顶手续费", "开户行许可证号", "贷记卡单笔限额 ", "贷记卡单日限额",
					"贷记卡单月限额", "借记卡单笔限额", "借记卡单日限额", " 借记卡单月限额", "风控级别" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);

			List<Object> objList = baseDao.queryAll("from Cycleinfo ");
			List<Cycleinfo> cycList = new ArrayList<Cycleinfo>();
			for (int i = 0; i < objList.size(); i++) {
				Cycleinfo cyc = (Cycleinfo) objList.get(i);
				cycList.add(cyc);
			}
			for (int i = 0; i < merinfoList.size(); i++) {
				MerinfoSearchBean objs = merinfoList.get(i);

				if (Constance.Null2EmptyTrim(objs.getMerKind()).equals("01")) {
					objs.setMerKind("企业单位");
				} else {
					objs.setMerKind("个体工商户");
				}
				// List<OrgBean> cycList=orgDao.listOrgs(op);

				for (int j = 0; j < cycList.size(); j++) {
					if (Constance.Null2EmptyTrim(objs.getCycleId()).equals(
							cycList.get(j).getCycleId())) {
						objs.setCycleId(cycList.get(j).getCycleName());
						break;
					}
				}

				if (Constance.Null2EmptyTrim(objs.getRateType()).equals("1")) {
					objs.setRateType("点数");
				} else if (Constance.Null2EmptyTrim(objs.getRateType()).equals(
						"3")) {
					objs.setRateType("封顶");
				}

				if (Constance.Null2EmptyTrim(objs.getCatdType()).equals("01")) {
					objs.setCatdType("身份证");
				} else if (Constance.Null2EmptyTrim(objs.getCatdType()).equals(
						"02")) {
					objs.setCatdType("护照");
				} else if (Constance.Null2EmptyTrim(objs.getCatdType()).equals(
						"03")) {
					objs.setCatdType("军官证");
				} else if (Constance.Null2EmptyTrim(objs.getCatdType()).equals(
						"04")) {
					objs.setCatdType("驾照");
				} else if (Constance.Null2EmptyTrim(objs.getCatdType()).equals(
						"05")) {
					objs.setCatdType("其他");
				}

				/*
				 * if(
				 * Constance.Null2EmptyTrim(objs.getMerStatus()).equals("A0")){
				 * objs.setMerStatus("正常"); }else
				 * if(Constance.Null2EmptyTrim(objs
				 * .getMerStatus()).equals("A1")){ objs.setMerStatus("提示级");
				 * }else
				 * if(Constance.Null2EmptyTrim(objs.getMerStatus()).equals(
				 * "A2")){ objs.setMerStatus("关注级"); }else
				 * if(Constance.Null2EmptyTrim
				 * (objs.getMerStatus()).equals("A3")){
				 * objs.setMerStatus("预警级"); }else
				 * if(Constance.Null2EmptyTrim(objs
				 * .getMerStatus()).equals("A4")){ objs.setMerStatus("拒绝级"); }
				 */

				String[] col_i = {
						Constance.Null2EmptyTrim(objs.getMid()),
						Constance.Null2EmptyTrim(objs.getMerName()),
						Constance.Null2EmptyTrim(objs.getMerBrName()),
						Constance.Null2EmptyTrim(objs.getMerKind()),
						Constance.Null2EmptyTrim(objs.getFlag()),
						Constance.Null2EmptyTrim(objs.getInterContact()),
						Constance.Null2EmptyTrim(objs.getPerPhone()),
						Constance.Null2EmptyTrim(objs.getPerName()),
						Constance.Null2EmptyTrim(objs.getCycleId()),
						Constance.Null2EmptyTrim(objs.getCatdType()),
						Constance.Null2EmptyTrim(objs.getCardNo()),
						Constance.Null2EmptyTrim(objs.getAddr()),
						Constance.Null2EmptyTrim(objs.getRealName()),
						Constance.Null2EmptyTrim(objs.getEngName()),
						Constance.Null2EmptyTrim(objs.getEngBrName()),
						Constance.Null2EmptyTrim(objs.getMcc()),
						Constance.Null2EmptyTrim(objs.getSetTime()),
						Constance.Null2EmptyTrim(objs.getRateType()),
						Constance.Null2EmptyTrim(objs.getRate()) + "%",
						Constance.Null2EmptyTrim(objs.getAmt() + "元"),
						Constance.Null2EmptyTrim(objs.getBus_lic()),
						Constance.Null2EmptyTrim(objs.getAmount1() + "元"),
						Constance.Null2EmptyTrim(objs.getDayTotalAmt1()) + "元",
						Constance.Null2EmptyTrim(objs.getMonthTotalAmt1())
								+ "元",
						Constance.Null2EmptyTrim(objs.getAmount2()) + "元",
						Constance.Null2EmptyTrim(objs.getDayTotalAmt2()) + "元",
						Constance.Null2EmptyTrim(objs.getMonthTotalAmt2())
								+ "元",
						Constance.Null2EmptyTrim(objs.getMerStatus()) };

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出商户详情Excel失败######", e);
			throw new ServiceException("导出商户详情Excel失败！");
		}
		return wb;
	}

	@Override
	public HSSFWorkbook settingTotalListTransQueryExcel(
			List<SettingBean> settingList, String startDate, String endDate)
			throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("结算明细");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("结算明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = {
					"报表日期：",
					Constance.formateDate(),
					"",
					"结算日期",
					startDate.replace("-", "") + "至" + endDate.replace("-", ""),
					"", "", "", "", "", "" };
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "序号", "商户号", "商户名称", "结算日期", "总金额",
					"总笔数", "商户手续费", "结算金额", "结算状态", "冻结状态", "结算批次" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			Double amt = 0.0;// 总金额
			Double feeAmt = 0.0;// 总手续费

			for (int i = 0; i < settingList.size(); i++) {
				SettingBean objs = settingList.get(i);

				if (Constance.Null2EmptyTrim(objs.getSettleflag()).equals("Y")) {
					objs.setSettleflag("待审核");
				} else if (Constance.Null2EmptyTrim(objs.getSettleflag())
						.equals("W")) {
					objs.setSettleflag("待出款");
				} else if (Constance.Null2EmptyTrim(objs.getSettleflag())
						.equals("D")) {
					objs.setSettleflag("出款中");
				} else if (Constance.Null2EmptyTrim(objs.getSettleflag())
						.equals("S")) {
					objs.setSettleflag("出款成功");
				} else {
					objs.setSettleflag("出款失败");
				}

				if (Constance.Null2EmptyTrim(objs.getFreezeflag()).equals("Y")) {
					objs.setFreezeflag("已冻结");
				} else if (Constance.Null2EmptyTrim(objs.getFreezeflag())
						.equals("Q")) {
					objs.setFreezeflag("冻结申请");
				} else {
					objs.setFreezeflag("未冻结");
				}

				String[] col_i = {
						Constance.Null2EmptyTrim(objs.getOrderno()),
						Constance.Null2EmptyTrim(objs.getMerchantno())
								.substring(
										3,
										Constance.Null2EmptyTrim(
												objs.getMerchantno()).length()),
						Constance.Null2EmptyTrim(objs.getMerchantname()),
						Constance.Null2EmptyTrim(objs.getSettledate()),
						Constance.Null2EmptyTrim(objs.getTotalamt()),
						Constance.Null2EmptyTrim(objs.getTotalnum()),
						Constance.Null2EmptyTrim(objs.getMerchantfee()),
						Constance.Null2EmptyTrim(objs.getMerchantsettleamt()),
						Constance.Null2EmptyTrim(objs.getSettleflag()),
						Constance.Null2EmptyTrim(objs.getFreezeflag()),
						Constance.Null2EmptyTrim(objs.getBatchno()) };

				feeAmt = Double.parseDouble(Constance.null2Amt(objs
						.getMerchantfee())) + feeAmt;
				amt = Double.parseDouble(Constance.null2Amt(objs
						.getMerchantsettleamt())) + amt;
				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

			DecimalFormat df = new DecimalFormat("0.00");
			String[] coloumValue = { "汇总：", "", "", "", "", "", "", "", "", "",
					"" };
			row = ExcelUtil.createRow(sheet, settingList.size() + 3,
					coloumValue, general_style, true);

			// String[]
			// coloumValue8={"交易总金额：",Double.toString(jyAmt).trim(),"交易总手续费：",Double.toString(feeAmt),"总结算金额：",Double.toString(amt)};
			String[] coloumValue8 = { "交易总金额：", df.format(amt), "交易总手续费：",
					df.format(feeAmt), "", "", "", "", "", "", "" };

			row = ExcelUtil.createRow(sheet, settingList.size() + 4,
					coloumValue8, general_style, true);
		} catch (Exception e) {
			logger.error("########导出结算Excel明细失败######", e);
			throw new ServiceException("导出结算Excel明细失败！");
		}
		return wb;
	}

	@Override
	public HSSFWorkbook estransQueryExcel(List<Estrnlog> estransList,
			String date) throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("交易明细");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("交易明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"交易日期", date.replace("-", ""), "", "", "", "", "", "", "",
					"", "", "" };
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "交易日期", "交易时间", "交易类型", "交易结果", "状态",
					"交易账号", "卡种", "发卡行", "交易金额", "商户名", "商户号", "终端号", "批次号",
					"流水号", "系统参考号" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			Double amt = 0.0;// 总金额
			Double feeAmt = 0.0;// 总手续费

			for (int i = 0; i < estransList.size(); i++) {
				Estrnlog objs = estransList.get(i);

				String[] col_i = { Constance.Null2EmptyTrim(objs.getEtsDate()),
						Constance.Null2EmptyTrim(objs.getEtsTime()),
						Constance.Null2EmptyTrim(objs.getEtstranCode()),
						Constance.Null2EmptyTrim(objs.getRespCode()),
						Constance.Null2EmptyTrim(objs.getReversal()),
						Constance.Null2EmptyTrim(objs.getAccount()),
						Constance.Null2EmptyTrim(objs.getCardName()),
						Constance.Null2EmptyTrim(objs.getBankName()),
						Constance.isMoney(objs.getAmount()),
						Constance.Null2EmptyTrim(objs.getMerName()),
						Constance.Null2EmptyTrim(objs.getMerNo()),
						Constance.Null2EmptyTrim(objs.getEtsterMid()),
						Constance.Null2EmptyTrim(objs.getPeriod()),
						Constance.Null2EmptyTrim(objs.getTermtrace()),
						Constance.Null2EmptyTrim(objs.getEtstrace()) };

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出交易Excel明细失败######", e);
			throw new ServiceException("导出交易Excel明细失败！");
		}
		return wb;

	}

	@Override
	public HSSFWorkbook duiZhangQueryExcel(List<Object> duiZhangList,
			String startDate, String endDate) throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("差错对账明细");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("差错对账明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中

			String[] coloumValue1 = {
					"报表日期：",
					Constance.formateDate(),
					"",
					"对账日期",
					startDate.replace("-", "") + "至" + endDate.replace("-", ""),
					"", "", "", "", "", "", "", "", "" };

			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "机构名称", "交易返回码", "商户号", "终端号", "对账类型",
					"卡号", "交易金额", "交易日期", "交易时间", "流水号", "通道交易日期", "通道交易时间",
					"通道流水号", "创建时间" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);

			for (int i = 0; i < duiZhangList.size(); i++) {
				DuizhangBean objs = (DuizhangBean) duiZhangList.get(i);

				if (Constance.Null2EmptyTrim(objs.getDiffflag()).equals("1")) {
					objs.setDiffflag("通道有本地无");
				} else if (Constance.Null2EmptyTrim(objs.getDiffflag()).equals(
						"2")) {
					objs.setDiffflag("本地有通道无");
				}

				String[] col_i = {
						Constance.Null2EmptyTrim(objs.getAccbranchname()),
						Constance.Null2EmptyTrim(objs.getServcode()),
						Constance.Null2EmptyTrim(objs.getHostmerchid()),
						Constance.Null2EmptyTrim(objs.getHosttermid()),
						Constance.Null2EmptyTrim(objs.getDiffflag()),
						Constance.Null2EmptyTrim(objs.getAccount()),
						Constance.isMoney(objs.getTraamt()),
						Constance.Null2EmptyTrim(objs.getReqdate()),
						Constance.Null2EmptyTrim(objs.getReqtime()),
						Constance.Null2EmptyTrim(objs.getReqlogno()),
						Constance.Null2EmptyTrim(objs.getLocaldate()),
						Constance.Null2EmptyTrim(objs.getLocaltime()),
						Constance.Null2EmptyTrim(objs.getLocallogno()),
						Constance.Null2EmptyTrim(objs.getInsertt()) };
				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出差错对账Excel明细失败######", e);
			throw new ServiceException("导出差错对账Excel明细失败！");
		}
		return wb;
	}

	@Override
	public HSSFWorkbook susMerinfoExcel(List<susesMerinfo> sList)
			throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("风险商户明细");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("风险商户明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "", "",
					"" };
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "营业执照号", "法人证件号", "商户名", "所属行业", "所属地区",
					"备注" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);

			for (int i = 0; i < sList.size(); i++) {
				susesMerinfo objs = sList.get(i);

				String[] col_i = {
						Constance.Null2EmptyTrim(objs.getBusinessLiscences()),
						Constance.Null2EmptyTrim(objs.getCardNo()),
						Constance.Null2EmptyTrim(objs.getMerinfo()),
						Constance.Null2EmptyTrim(objs.getBusiness()),
						Constance.Null2EmptyTrim(objs.getAreaCode()),
						Constance.Null2EmptyTrim(objs.getCol1()) };

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出可疑Excel明细失败######", e);
			throw new ServiceException("导出可疑Excel明细失败！");
		}
		return wb;

	}

	@Override
	public HSSFWorkbook blackListExcel(List<BlackList> sList)
			throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("黑名单明细");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("黑名单明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "", "",
					"", "", "", "", "", "", "", "" };
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "银行卡号", "身份证号", "营业执照号", "商户号", "终端号",
					"拉黑类别", "解锁次数", "状态", "操作员", "加入时间", "更新时间", "备注" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);

			for (int i = 0; i < sList.size(); i++) {
				BlackList objs = sList.get(i);

				if (Constance.Null2EmptyTrim(objs.getIsused()).equals("Y")) {
					objs.setIsused("启用");
				} else if (Constance.Null2EmptyTrim(objs.getIsused()).equals(
						"N")) {
					objs.setIsused("不启用");
				}

				String[] col_i = { Constance.Null2EmptyTrim(objs.getAcctNo()),
						Constance.Null2EmptyTrim(objs.getCardNo()),
						Constance.Null2EmptyTrim(objs.getLicenseNo()),
						Constance.Null2EmptyTrim(objs.getMid()),
						Constance.Null2EmptyTrim(objs.getTermId()),
						Constance.Null2EmptyTrim(objs.getAddType()),
						Constance.Null2EmptyTrim(objs.getUnlockTimes()),
						Constance.Null2EmptyTrim(objs.getIsused()),
						Constance.Null2EmptyTrim(objs.getOperators()),
						Constance.Null2EmptyTrim(objs.getDtCreate()),
						Constance.Null2EmptyTrim(objs.getDtLastmod()),
						Constance.Null2EmptyTrim(objs.getRemark()) };

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出黑名单Excel明细失败######", e);
			throw new ServiceException("导出黑名单Excel明细失败！");
		}
		return wb;
		// return null;
	}

	/**
	 * 导出分润明细
	 */
	@Override
	public HSSFWorkbook proFitShareDetailExcel(List<Object> searchRsList,
			String minDate, String maxDate, Operators op)
			throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("代理商分润明细");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("代理商分润明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中

			String[] coloumValue1 = { "账户：", op.getAccountNo(), "姓名:",
					op.getRealName(), "", "", "", "", "", "", "" };
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue2 = { "报表日期：", minDate + "-" + maxDate, "", "",
					"", "", "", "", "", "", "" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue2, general_style,
					true);

			String[] coloumValue3 = { "商户名称", "商户号", "业务类型", "交易日期", "交易时间",
					"账号", "交易金额(元)", "分润金额(元)", "手续费", "归属代理商", "上级代理商" };
			row = ExcelUtil.createRow(sheet, 3, coloumValue3, data_style, true);

			for (int i = 0; i < searchRsList.size(); i++) {
				Object[] objs = (Object[]) searchRsList.get(i);

				String[] col_i = {
						Constance.Null2EmptyTrim(objs[2]),
						Constance.Null2EmptyTrim(objs[1]),
						Constance.Null2EmptyTrim(objs[23]),
						Constance.Null2EmptyTrim(objs[14]),
						Constance.Null2EmptyTrim(objs[15]),
						Constance.Null2EmptyTrim(objs[16]),
						Constance.Null2EmptyTrim(objs[12]),
						Constance.Null2EmptyTrim(new BigDecimal(Constance
								.Null2EmptyTrim(objs[24]))),
						Constance.Null2EmptyTrim(objs[11]),
						Constance.Null2EmptyTrim(objs[8]),
						Constance.Null2EmptyTrim(objs[9]) };

				row = ExcelUtil.createRow(sheet, 4 + i, col_i, general_style,
						true);
			}

		} catch (Exception e) {
			logger.error("########导出代理商分润明细Excel明细失败######", e);
			throw new ServiceException("导出代理商分润明细Excel明细失败！");
		}
		return wb;
		// return null;
	}

	@Override
	public HSSFWorkbook daiLIExcel(List<MerchantReport> sList,
			String startDate, String endDate) throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("代理商交易统计");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("代理商月交易统计"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中

			String[] coloumValue1 = {
					"报表日期：",
					Constance.formateDate(),
					"",
					"交易日期",
					startDate.replace("-", "") + "至" + endDate.replace("-", ""),
					"", "", "" };

			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "机构名称", "代理商账号", "代理商名称", "行业类型",
					"总交易金额", "总手续费", "总笔数", "交易日期" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);

			for (int i = 0; i < sList.size(); i++) {
				MerchantReport objs = (MerchantReport) sList.get(i);

				String[] col_i = { Constance.Null2EmptyTrim(objs.getOrgCode()),
						Constance.Null2EmptyTrim(objs.getAccountNo()),
						Constance.Null2EmptyTrim(objs.getRealName()),
						Constance.Null2EmptyTrim(objs.getMccType()),
						Constance.Null2EmptyTrim(objs.getTotalAmt()),
						Constance.Null2EmptyTrim(objs.getTotalFee()),
						Constance.Null2EmptyTrim(objs.getTotalNum()),
						Constance.Null2EmptyTrim(objs.getTransDate()) };
				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出代理商月交易统计失败######", e);
			throw new ServiceException("导出代理商月交易统计失败！");
		}
		return wb;
	}

	@Override
	public SXSSFWorkbook  exportTransFlow(List<TblTfrTrnLog> tftList,
			String minDate,String maxDate,Map<String, String> resMap,Operators op) throws ServiceException {

		// TODO Auto-generated method stub
		SXSSFWorkbook  wb = new SXSSFWorkbook ();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("交易明细");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 14);
			sheet.addMergedRegion(region);
			cell.setCellValue("交易明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(),
					"交易日期", minDate, maxDate,"交易成功总金额", resMap.get("totalAmt"),"交易成功总笔数", resMap.get("totalCount"), "撤销成功总金额", resMap.get("cxTotalAmt"),"撤销成功总笔数",resMap.get("cxTotalCount"),"","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			/*String[] coloumValue3 = { "交易日期", "交易时间", "交易类型", "交易结果", "是否被处理","交易账号",
					"卡类型", "交易金额", "商户名", "商户号", "终端号", "流水号", "系统参考号" };*/
			
			if("03".equals(op.getRoleType())){
					String[] coloumValue3 = { "商户号", "商户名", "终端号", "交易账号", "交易金额(元)",
							"交易类型", "银联返回码", "交易结果", "交易状态","交易日期", "交易时间", "卡类型","接触方式", "流水号", "系统参考号","系统跟踪号","交易参考号"};
					row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			}else{
					String[] coloumValue3 = { "商户号", "商户名", "终端号", "交易账号", "交易金额(元)",
							"交易类型", "银联返回码", "交易结果", "交易状态","交易日期", "交易时间", "卡类型","接触方式", "流水号", "系统参考号","系统跟踪号","交易参考号","银联商户号" };
					row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			}	
					
				
				
			
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < tftList.size(); i++) {
				TblTfrTrnLog objs = tftList.get(i);

//				System.out.println("金额：" + objs.getAmountConver());

				/*String[] col_i = { Constance.Null2EmptyTrim(objs.getTransDt()),
						Constance.Null2EmptyTrim(objs.getTransMt()),
						Constance.Null2EmptyTrim(objs.getTransInf()),
						Constance.Null2EmptyTrim(objs.getRespName()),
						Constance.Null2EmptyTrim(objs.getPriAcctNo()),
						Constance.Null2EmptyTrim(objs.getCardType()),
						Constance.isMoney(objs.getAmountConver()),
						Constance.Null2EmptyTrim(objs.getMchntNmConv()),
						Constance.Null2EmptyTrim(objs.getMchntCd()),
						Constance.Null2EmptyTrim(objs.getTermId()),
						Constance.Null2EmptyTrim(objs.getMaTransSeq()),
						Constance.Null2EmptyTrim(objs.getRetriRefNoConv()) };*/
				if("03".equals(op.getRoleType())){
					String[] col_i = { Constance.Null2EmptyTrim(objs.getMchntCd()),
							Constance.Null2EmptyTrim(objs.getMchntNmConv()),
							Constance.Null2EmptyTrim(objs.getTermId()),
							Constance.Null2EmptyTrim(objs.getPriAcctNo()),
							Constance.Null2EmptyTrim(objs.getTransAt()),
							Constance.Null2EmptyTrim(objs.getTransInf()),
							Constance.Null2EmptyTrim(objs.getRespCd()),
							Constance.Null2EmptyTrim(objs.getRespName()),
							Constance.Null2EmptyTrim(objs.getDealRs()),
							Constance.Null2EmptyTrim(objs.getTransDt()),
							Constance.Null2EmptyTrim(objs.getTransMt()),
							Constance.Null2EmptyTrim(objs.getCardType()),
							Constance.Null2EmptyTrim(objs.getPosEntryMdCdName()),
							Constance.Null2EmptyTrim(objs.getMaTransSeq()),
							Constance.Null2EmptyTrim(objs.getKeyRsp()),
							Constance.Null2EmptyTrim(objs.getRetriRefNo()),
							Constance.Null2EmptyTrim(objs.getRetriRefNoConv())
							};
					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
				}else{
					String[] col_i = { Constance.Null2EmptyTrim(objs.getMchntCd()),
							Constance.Null2EmptyTrim(objs.getMchntNmConv()),
							Constance.Null2EmptyTrim(objs.getTermId()),
							Constance.Null2EmptyTrim(objs.getPriAcctNo()),
							Constance.Null2EmptyTrim(objs.getTransAt()),
							Constance.Null2EmptyTrim(objs.getTransInf()),
							Constance.Null2EmptyTrim(objs.getRespCd()),
							Constance.Null2EmptyTrim(objs.getRespName()),
							Constance.Null2EmptyTrim(objs.getDealRs()),
							Constance.Null2EmptyTrim(objs.getTransDt()),
							Constance.Null2EmptyTrim(objs.getTransMt()),
							Constance.Null2EmptyTrim(objs.getCardType()),
							Constance.Null2EmptyTrim(objs.getPosEntryMdCdName()),
							Constance.Null2EmptyTrim(objs.getMaTransSeq()),
							Constance.Null2EmptyTrim(objs.getKeyRsp()),
							Constance.Null2EmptyTrim(objs.getRetriRefNo()),
							Constance.Null2EmptyTrim(objs.getRetriRefNoConv()),
							Constance.Null2EmptyTrim(objs.getMchntCdConv())
							};
					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
				}
				
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出交易Excel明细失败######", e);
			throw new ServiceException("导出交易Excel明细失败！");
		}
		return wb;

	}

	/**
	 * 导出 代付流水
	 * 
	 * @param searchCondition
	 * @return
	 * @throws ServiceException
	 */
	public HSSFWorkbook exportNegotiation(List<Negotiation> date, Operators op,
			List<String> conditions) throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("代付流水");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 16, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 14, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			

			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("代付流水"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = (String[]) conditions
					.toArray(new String[conditions.size()]);
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue2 = { "交易日期", "交易时间", "处理状态", "交易流水号", "商户代码",
					"商户名称", "收款行名称", "收款人账号", "收款人名称",  "付款金额（元）" };
//, "手续费（元）", "结算金额（元）"付款人账号","付款人名称"
			row = ExcelUtil.createRow(sheet, 2, coloumValue2, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < date.size(); i++) {
				Negotiation objs = date.get(i);
				/*
				 * String[] str1 = new String[14]; if
				 * ("0000000000".equals(objs.getProcSt())) { str1[2] = "代付成功"; }
				 * else if ("0000000001".equals(objs.getProcSt())) { str1[2] =
				 * "代付失败";
				 * 
				 * }
				 */
				String[] col_i = { Constance.Null2EmptyTrim(objs.getTransDt()),
						Constance.Null2EmptyTrim(objs.getTransTm()),
						Constance.Null2EmptyTrim(objs.getRespCd()),
						Constance.Null2EmptyTrim(objs.getTranSeq()),
						Constance.Null2EmptyTrim(objs.getMchntCd()),
						Constance.Null2EmptyTrim(objs.getMchntNm()),
						Constance.Null2EmptyTrim(objs.getPyeBankNm()),
						Constance.Null2EmptyTrim(objs.getPyeAcctNo()),
						Constance.Null2EmptyTrim(objs.getPyeAcctNm()),
						/*Constance.Null2EmptyTrim(objs.getPriBankNm()),*/
						/*Constance.Null2EmptyTrim(objs.getPriAcctNo()),
						Constance.Null2EmptyTrim(objs.getPriAcctNm()),*/
						String.valueOf(Double.valueOf(Constance.Null2EmptyTrim(objs.getTranAmt()))/100)/*,
						String.valueOf(Double.valueOf(Constance.Null2EmptyTrim(objs.getStlmFee()))/100),
						String.valueOf(Double.valueOf(Constance.Null2EmptyTrim(objs.getStlmFee()))/100),*/

				};
				int length = col_i.length;
				sheet.setColumnWidth((short) 3, (short) (length * 1100));
				sheet.setColumnWidth((short) 6, (short) (length * 500));

				if ("00".equals(col_i[2])) {
					col_i[2] = "代付成功";
				} else if("G9".equals(col_i[2])) {
					col_i[2] = "代付成功";
				}
				else  /*("00".equals(col_i[2]))*/ {
					col_i[2] = "代付失败";

				}

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
			}
			//修改单元格宽度
			/*sheet.setColumnWidth(3, "交易流水号".getBytes().length*3*256);*/

			/*
			 * String[] strs = new String[1]; strs[0] = "交易时间"; if
			 * ("000".equals(objs.getAccessMd())) { strs[5] = "代付成功"; } else if
			 * ("001".equals(objs.getAccessMd())) { strs[5] = "代付失败"; }
			 */
			/*
			 * System.out.println("金额：" + objs.getAmountConver());
			 * 
			 * String[] col_i = { Constance.Null2EmptyTrim(objs.getTransDt()),
			 * Constance.Null2EmptyTrim(objs.getTransMt()),
			 * Constance.Null2EmptyTrim(objs.getTransInf()),
			 * Constance.Null2EmptyTrim(objs.getRespName()),
			 * Constance.Null2EmptyTrim(objs.getPriAcctNo()),
			 * Constance.Null2EmptyTrim(objs.getCardType()),
			 * Constance.isMoney(objs.getAmountConver()),
			 * Constance.Null2EmptyTrim(objs.getMchntNmConv()),
			 * Constance.Null2EmptyTrim(objs.getMchntCd()),
			 * Constance.Null2EmptyTrim(objs.getTermId()),
			 * Constance.Null2EmptyTrim(objs.getMaTransSeq()),
			 * Constance.Null2EmptyTrim(objs.getRetriRefNoConv()) };
			 * 
			 * row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
			 * true);
			 * 
			 * 
			 * }
			 */

		} catch (Exception e) {
			logger.error("########导出代付流水Excel失败######", e);
			throw new ServiceException("导出代付流水Excel明细失败！");
		}
		return wb;
	}

	@Override
	public HSSFWorkbook clearTransQueryExcel(List<TblClearTxn> clearList,
			String startDate, String endDate) throws ServiceException {
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("清算对账");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 12);
			sheet.addMergedRegion(region);
			cell.setCellValue("清算明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = {
					"报表日期：",
					Constance.formateDate(),
					"",
					"清算日期",
					startDate.replace("-", "") + "至" + endDate.replace("-", ""),
					"", "", "", "", "", "", "", "" };
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "商户号", "商户名称", "终端号", "系统跟踪号", "卡号", "交易日期", "交易时间", "交易金额", "商户手续费用", "总结算金额",
					"清算日期" ,"交易类型","卡类型"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			Double amt = 0.0;// 总金额
			Double jyAmt = 0.0;// 交易总金额
			Double feeAmt = 0.0;// 总手续费
			for (int i = 0; i < clearList.size(); i++) {
				TblClearTxn objs = clearList.get(i);
				String[] col_i = { Constance.Null2EmptyTrim(objs.getMchtCd()),
						Constance.Null2EmptyTrim(objs.getMchtName()),
						Constance.Null2EmptyTrim(objs.getTermId()),
						Constance.Null2EmptyTrim(objs.getSysSer()),
						Constance.Null2EmptyTrim(objs.getPan()),
						Constance.Null2EmptyTrim(objs.getStlmDate()),
						Constance.Null2EmptyTrim(objs.getTransDateTime()),
						Constance.Null2EmptyTrim(objs.getTransAmt()),
						Constance.Null2EmptyTrim(objs.getMchtFee()),
						Constance.Null2EmptyTrim(objs.getMchtSetAmt()),
						Constance.Null2EmptyTrim(objs.getMaSettleDtStr()), 
						Constance.Null2EmptyTrim(objs.getTxnDesc()), 
						Constance.Null2EmptyTrim(objs.getCardKindDis())};
				jyAmt = Double.parseDouble(Constance.null2Amt(objs
						.getTransAmt())) + jyAmt;
				feeAmt = Double.parseDouble(Constance.null2Amt(objs
						.getMchtFee())) + feeAmt;
				amt = Double.parseDouble(Constance.null2Amt(objs
						.getMchtSetAmt())) + amt;
				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
			}

			DecimalFormat df = new DecimalFormat("0.00");
			String[] coloumValue = { "汇总：", "", "", "", "", "", "", "", "", "", "" , "", ""};
			row = ExcelUtil.createRow(sheet, clearList.size() + 3, coloumValue,
					general_style, true);

			String[] coloumValue8 = { "交易总金额：", df.format(jyAmt), "交易总手续费：",
					df.format(feeAmt), "总结算金额：", df.format(amt), "", "", "",
					"", "", "" };

			row = ExcelUtil.createRow(sheet, clearList.size() + 4,
					coloumValue8, general_style, true);

		} catch (Exception e) {
			logger.error("########导出清算Excel明细失败######", e);
			throw new ServiceException("导出清算Excel明细失败！");
		}
		return wb;
	}

	@Override
	public HSSFWorkbook statisticsAgentTransQueryExcel(List<Operators> operList,
			String startDate, String endDate) throws ServiceException {
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet( "审核汇总导出" );
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 6);// [(short) 0,(short) 6] 合并 0-6 这几列单元格
			sheet.addMergedRegion(region);
			cell.setCellValue(startDate + "-" + endDate); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			
			String[] coloumValue3 = {"区域","代理商", "通过", "退回","审核中","未审核", "共计"};
			row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, true);
			Double allCount1 = 0.0;// 总数量
			Double allCount2 = 0.0;// 通过总数量
			Double allCount3 = 0.0;// 退回总数量
			Double allCount4 = 0.0;// 审核中总数量
			Double allCount5 = 0.0;// 未审核总数量
			for (int i = 0; i < operList.size(); i++) {
				Operators objs = operList.get(i);
				String[] col_i = { Constance.Null2EmptyTrim(objs.getCompanyName()),
						Constance.Null2EmptyTrim(objs.getRealName()),
						Constance.Null2EmptyTrim(objs.getCount2()),
						Constance.Null2EmptyTrim(objs.getCount3()),
						Constance.Null2EmptyTrim(objs.getCount4()),
						Constance.Null2EmptyTrim(objs.getCount5()),
						Constance.Null2EmptyTrim(objs.getCount1())};
				allCount2 = Double.parseDouble(Constance.null2Amt(objs
						.getCount2())) + allCount2;
				allCount3 = Double.parseDouble(Constance.null2Amt(objs
						.getCount3())) + allCount3;
				allCount4 = Double.parseDouble(Constance.null2Amt(objs
						.getCount4())) + allCount4;
				allCount5 = Double.parseDouble(Constance.null2Amt(objs
						.getCount5())) + allCount5;
				allCount1 = Double.parseDouble(Constance.null2Amt(objs
						.getCount1())) + allCount1;
				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						true);
			}

			DecimalFormat df = new DecimalFormat("0");
			String[] coloumValue = { "总计：","", df.format(allCount2), df.format(allCount3), df.format(allCount4), df.format(allCount5), df.format(allCount1)};
			row = ExcelUtil.createRow(sheet, operList.size() + 2, coloumValue,
					general_style, true);

		} catch (Exception e) {
			logger.error("########审核汇总导出Excel明细失败######", e);
			throw new ServiceException("审核汇总导出Excel明细失败！");
		}
		return wb;
	}

	@Override
	public HSSFWorkbook statisticsAgentAreaTransQueryExcel(List<Operators> operList,
			String startDate, String endDate) throws ServiceException {
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet( "审核区域汇总导出" );
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 5);// [(short) 0,(short) 6] 合并 0-6 这几列单元格
			sheet.addMergedRegion(region);
			cell.setCellValue(startDate + "-" + endDate); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			
			String[] coloumValue3 = {"区域", "通过", "退回","审核中","未审核", "共计"};
			row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, true);
			Double allCount1 = 0.0;// 总数量
			Double allCount2 = 0.0;// 通过总数量
			Double allCount3 = 0.0;// 退回总数量
			Double allCount4 = 0.0;// 审核中总数量
			Double allCount5 = 0.0;// 未审核总数量
			for (int i = 0; i < operList.size(); i++) {
				Operators objs = operList.get(i);
				String[] col_i = { Constance.Null2EmptyTrim(objs.getCompanyName()),
					//	Constance.Null2EmptyTrim(objs.getRealName()),
						Constance.Null2EmptyTrim(objs.getCount2()),
						Constance.Null2EmptyTrim(objs.getCount3()),
						Constance.Null2EmptyTrim(objs.getCount4()),
						Constance.Null2EmptyTrim(objs.getCount5()),
						Constance.Null2EmptyTrim(objs.getCount1())};
				allCount2 = Double.parseDouble(Constance.null2Amt(objs
						.getCount2())) + allCount2;
				allCount3 = Double.parseDouble(Constance.null2Amt(objs
						.getCount3())) + allCount3;
				allCount4 = Double.parseDouble(Constance.null2Amt(objs
						.getCount4())) + allCount4;
				allCount5 = Double.parseDouble(Constance.null2Amt(objs
						.getCount5())) + allCount5;
				allCount1 = Double.parseDouble(Constance.null2Amt(objs
						.getCount1())) + allCount1;
				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						true);
			}

			DecimalFormat df = new DecimalFormat("0");
			String[] coloumValue = { "总计：", df.format(allCount2), df.format(allCount3), df.format(allCount4), df.format(allCount5), df.format(allCount1)};
			row = ExcelUtil.createRow(sheet, operList.size() + 2, coloumValue,
					general_style, true);

		} catch (Exception e) {
			logger.error("########审核区域汇总导出Excel明细失败######", e);
			throw new ServiceException("审核区域汇总导出Excel明细失败！");
		}
		return wb;
	}


	@Override
	public HSSFWorkbook statisticsMerinfoTransQueryExcel(List<TblMchtInf> mchtList,
			String startDate, String endDate) throws ServiceException{
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet( "商户信息导出" );
			setStatMerColWidth(sheet); // 设置每列宽度
			
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			general_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中    
			
			general_style.setWrapText(true);//设置自动换行
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 27);
			sheet.addMergedRegion(region);
			cell.setCellValue(startDate + "-" + endDate); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			
			String[] coloumValue3 = {"日期", "银联商户号", "银联终端号", "商户简称", "工商注册名称", "营业执照编号", "结算方式", "所属机构", "所属代理商", 
									"审核人员", "状态", "业务联系人", "业务联系人电话", "法人", "法人证件号", "MCC", "43域", "结算银行", "联行号", 
									"结算银行账号名称", "结算银行账号", "商户地址",  "商户类型",  "境内借记卡手续费", "境内贷记卡手续费",  "终端类型", 
									"终端安装地址", "	终端风控"
					};
			row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, false); // widthFlag : true 设置宽度, false 不设置宽度
			Double allCount1 = 0.0;// 总数量
			Double allCount2 = 0.0;// 通过总数量
			Double allCount3 = 0.0;// 退回总数量
			for (int i = 0; i < mchtList.size(); i++) {
				TblMchtInf objs = mchtList.get(i);
				List<TblTermInf> terminfoList= objs.getTerminfoList();
				
				List<TblTermRiskCfg> riskList = terminfoList.get(0).getRiskCfgList();//终端风控
				StringBuffer riskStr = new StringBuffer();
				if(null != riskList && riskList.size() > 0){
					for (TblTermRiskCfg tblTermRiskCfg : riskList) {
						if(tblTermRiskCfg.getId().getCardType().equals("00")){
							riskStr.append("贷记卡:");
						}else if(tblTermRiskCfg.getId().getCardType().equals("01")){
							riskStr.append("借记卡:");
						}
						riskStr.append("单笔下限:" + tblTermRiskCfg.getSingleMinMoney());
						riskStr.append("---单笔上限:" + tblTermRiskCfg.getSingleLimitmoney());
						riskStr.append("---单日累计限额:" + tblTermRiskCfg.getTotalLimitmoney());
						riskStr.append("\r\n");
					}
				}
				
				List<TblMchtBizFee>  feeInfo = objs.getFee(); //手续费
				StringBuffer dfeeStr = new StringBuffer();// 贷记卡费率
				StringBuffer jfeeStr = new StringBuffer();// 借记卡费率
				if(null != feeInfo && feeInfo.size() > 0){
					for (TblMchtBizFee tblMchtBizFee : feeInfo) {
						StringBuffer feeStr = new StringBuffer();// 贷记卡费率
						feeStr.append(tblMchtBizFee.getMchtFeeMd() + ":" );
						feeStr.append((null == tblMchtBizFee.getMchtFeePercent() ? "" : tblMchtBizFee.getMchtFeePercent()) + "% - " 
									+ (tblMchtBizFee.getMchtFeePctMax() == null ? "" : tblMchtBizFee.getMchtFeePctMax()) + "封顶");
						
						if(tblMchtBizFee.getMchtFeeMd().contains("借记卡")){
							jfeeStr.append(feeStr.toString() + "\r\n");
						}else{
							dfeeStr.append(feeStr.toString() + "\r\n");
						}

					}
				}
				
				TblMchtBankAccount bankAccountInfo = objs.getBankAccount();//结算账户信息
				// 结算周期
				String[]  merCycle = objs.getMerCycle();
				StringBuffer cy = new StringBuffer();
				if(null != merCycle && merCycle.length > 0){
					for (int m = 0; m < merCycle.length; m++) {
						if(m == 1 && merCycle[m] != null){
							cy.append("+");
						}
						cy.append( (null == merCycle[m] ? "" : merCycle[m])+ "\r\n");
					}
				}
				
				String[] col_i = { Constance.Null2EmptyTrim(objs.getApplDate()),
						//	objs.getMCHTCD ,
						Constance.Null2EmptyTrim(objs.getId()) ,
						Constance.Null2EmptyTrim(terminfoList.get(0).getId().getTermId()) , 
						Constance.Null2EmptyTrim(objs.getNameBusi()) , 
						Constance.Null2EmptyTrim(objs.getName()), 
						Constance.Null2EmptyTrim(objs.getBusiLiceNo()), 
						Constance.Null2EmptyTrim(cy.toString()), 
						Constance.Null2EmptyTrim(objs.getBankBelongCd())	, 
						Constance.Null2EmptyTrim(objs.getExt1()), 
					//	objs.getFINALARRPOVALUSERNAME , // 审核人员
						Constance.Null2EmptyTrim(objs.getBus_lic()), 
						Constance.Null2EmptyTrim(objs.getStatus()) , 
						Constance.Null2EmptyTrim(objs.getContactName()) , 
						Constance.Null2EmptyTrim(objs.getContactPhoneNo()), 
						Constance.Null2EmptyTrim(objs.getCertif()) , 
						Constance.Null2EmptyTrim(objs.getCertifNo()) , 
						Constance.Null2EmptyTrim(objs.getUpMccCd()) , 
						// 43域
						"" , 
						// 结算信息 ---------->结算银行	联行号	结算银行账号名称	结算银行账号
						Constance.Null2EmptyTrim( (null == bankAccountInfo ? "" : bankAccountInfo.getBankName() ) ) , 
						Constance.Null2EmptyTrim( (null == bankAccountInfo ? "" : bankAccountInfo.getBankCode() ) ) , 
						Constance.Null2EmptyTrim( (null == bankAccountInfo ? "" : bankAccountInfo.getName() ) ) , 
						Constance.Null2EmptyTrim( (null == bankAccountInfo ? "" : bankAccountInfo.getAccount() ) ) , 
						Constance.Null2EmptyTrim(objs.getRegAddr()) ,
						// 商户类型
						"标准类" ,
						// 手续费信息-----------> 境内借记卡手续费	境内贷记卡手续费	产品手续费
						Constance.Null2EmptyTrim(jfeeStr.toString()) , 
						Constance.Null2EmptyTrim(dfeeStr.toString()) , 
						Constance.Null2EmptyTrim(terminfoList.get(0).getTermTp()) ,
						Constance.Null2EmptyTrim(terminfoList.get(0).getInstalllocation()),
						// 终端风控
						Constance.Null2EmptyTrim(riskStr.toString())
				};
				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						false);
			}
		} catch (Exception e) {
			logger.error("########商户信息导出Excel明细失败######", e);
			throw new ServiceException("商户信息导出Excel明细失败！");
		}
		return wb;
	
	}

	@Override
	public HSSFWorkbook exportMerinfoTransQueryExcel(List<TblMchtInf> mchtList,
			String startDate, String endDate) throws ServiceException{
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet( "商户信息导出" );
			setExportMerColWidth(sheet); // 设置每列宽度
			
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			general_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中    
			
			general_style.setWrapText(true);//设置自动换行
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 34);
			sheet.addMergedRegion(region);
			cell.setCellValue(startDate + "-" + endDate); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			
			String[] coloumValue3 = {"商户号", "所属机构", "商户来源渠道", "发展人推荐", "所属收单机构", "收单机构32域", "商户前三位机构代码", "商户号地区码", 
									"MCC", "商户是否银联标准", "商户工商注册名称", "营业名称", "商户执照号", "经营范围", "主营业务", "法人代表", "法人证件类型", "法人证件号", 
									"商户联系人姓名", "商户联系电话号码", "商户注册国家编码", "省代码", "市代码", "县代码", "地址", "结算账户名称", "结算账户", "结算账户开户行", 
									"结算账户银行联行号", "银联终端号", "非法人身份证", "直属代理商" , "所属分公司", "一级代理商", "审核人员"
					};
			row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, false); // widthFlag : true 设置宽度, false 不设置宽度
			for (int i = 0; i < mchtList.size(); i++) {
				TblMchtInf objs = mchtList.get(i);
				TblMchtBankAccount bankAccount= objs.getBankAccount();
				List<TblTermInf> terminfoList= objs.getTerminfoList();
				
				String[] col_i = { 
						
							Constance.Null2EmptyTrim(objs.getId()),
						       "48500000",  
						       "1", 
						       "代理商", 
							Constance.Null2EmptyTrim(objs.getBankBelongCd()) , 
							Constance.Null2EmptyTrim(objs.getUcBccd()) , 
					        "850", 
							Constance.Null2EmptyTrim(objs.getMchcdAreadCd()) , 
							Constance.Null2EmptyTrim(objs.getUpMccCd()) , 
						       "是" ,
							Constance.Null2EmptyTrim(objs.getName()) , 
							Constance.Null2EmptyTrim(objs.getNameBusi()) , 
							Constance.Null2EmptyTrim(objs.getBusiLiceNo()) , 
							Constance.Null2EmptyTrim(objs.getBusiRang()) , 
							Constance.Null2EmptyTrim(objs.getBusiMain()) , 
							Constance.Null2EmptyTrim(objs.getCertif()) , 
							Constance.Null2EmptyTrim(objs.getCertifType()) , 
							Constance.Null2EmptyTrim(objs.getCertifNo()) , 
							Constance.Null2EmptyTrim(objs.getContactName()) , 
							Constance.Null2EmptyTrim(objs.getContactPhoneNo()) , 
						       "CHN" ,
							Constance.Null2EmptyTrim(objs.getProvCd()) , 
							Constance.Null2EmptyTrim(objs.getCityCd()) , 
							Constance.Null2EmptyTrim(objs.getAreaCd()) , 
							Constance.Null2EmptyTrim(objs.getRegAddr()) , 
							Constance.Null2EmptyTrim(bankAccount.getName()) , 
							Constance.Null2EmptyTrim(bankAccount.getAccount()) , 
							Constance.Null2EmptyTrim(bankAccount.getBankName()) , 
							Constance.Null2EmptyTrim(bankAccount.getBankCode()) , 
							Constance.Null2EmptyTrim(terminfoList.get(0).getId().getTermId()) , 
							Constance.Null2EmptyTrim(objs.getStlmWayDesc()) , 
							Constance.Null2EmptyTrim(objs.getDirectlyAgent()) , 
							
							Constance.Null2EmptyTrim(objs.getCompanyName()) , 
							Constance.Null2EmptyTrim(objs.getOneagentName()) , 
							Constance.Null2EmptyTrim(objs.getBus_lic()) 
							
				};
				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						false);
			}
		} catch (Exception e) {
			logger.error("########商户信息导出Excel明细失败######", e);
			throw new ServiceException("商户信息导出Excel明细失败！");
		}
		return wb;
	
	}
	
	public void setStatMerColWidth(HSSFSheet sheet){

		
		sheet.setColumnWidth(0, 15 * 512); // 日期
		sheet.setColumnWidth(1, 15 * 512); // 银联商户号
		sheet.setColumnWidth(2, 15 * 512); // 银联终端号
		sheet.setColumnWidth(3, 15 * 512); // 商户简称"

		sheet.setColumnWidth(4, 40 * 512); // 工商注册名称

		sheet.setColumnWidth(5, 15 * 512); // 营业执照编号
		
		sheet.setColumnWidth(6, 10 * 256); // 结算方式

		sheet.setColumnWidth(7, 10 * 512); // 所属机构
		sheet.setColumnWidth(8, 20 * 512); // 所属代理商
		sheet.setColumnWidth(9, 10 * 512); // 审核人员
		sheet.setColumnWidth(10, 10 * 512); // 状态
		sheet.setColumnWidth(11, 10 * 512); // 业务联系人
		sheet.setColumnWidth(12, 20 * 512); // 业务联系人电话
		sheet.setColumnWidth(13, 10 * 512); // 法人
		
		sheet.setColumnWidth(14, 25 * 256); // 法人证件号
/*
		sheet.setColumnWidth(15, 2 * 256); // MCC
		sheet.setColumnWidth(16, 2 * 512); // 43域
*/			
		sheet.setColumnWidth(17, 40 * 512); // 结算银行

		sheet.setColumnWidth(18, 15 * 256); // 联行号
		sheet.setColumnWidth(19, 10 * 512); // 结算银行账号名称
		
		sheet.setColumnWidth(20, 25 * 256); // 结算银行账号
		sheet.setColumnWidth(21, 30 * 512); // 商户地址

		sheet.setColumnWidth(22, 10 * 512); // 商户类型
		
		sheet.setColumnWidth(23, 40 * 512); // 境内借记卡手续费
		sheet.setColumnWidth(24, 40 * 512); // 境内贷记卡手续费

		sheet.setColumnWidth(25, 10 * 512); // 终端类型
		
		sheet.setColumnWidth(26, 30 * 512); // 终端安装地址
		sheet.setColumnWidth(27, 40 * 512); // 终端风控
	}

	
	public void setExportMerColWidth(HSSFSheet sheet){

		sheet.setColumnWidth(0, 10 * 512); // 商户号
		sheet.setColumnWidth(1, 10 * 512); // 所属机构
		sheet.setColumnWidth(2, 10 * 512); // 商户来源渠道
		sheet.setColumnWidth(3, 10 * 512); // 发展人推荐
		sheet.setColumnWidth(4, 10 * 512); // 所属收单机构
		sheet.setColumnWidth(5, 10 * 512); // 收单机构32域
		sheet.setColumnWidth(6, 10 * 512); // 商户前三位机构代码
		sheet.setColumnWidth(7, 10 * 512); // 商户号地区码
		sheet.setColumnWidth(8, 10 * 512); // MCC
		sheet.setColumnWidth(9, 10 * 512); // 商户是否银联标准
		sheet.setColumnWidth(10, 10 * 512); // 商户工商注册名称
		sheet.setColumnWidth(11, 10 * 512); // 营业名称
		sheet.setColumnWidth(12, 10 * 512); // 商户执照号
		sheet.setColumnWidth(13, 10 * 512); // 经营范围
		sheet.setColumnWidth(14, 10 * 512); // 主营业务
		sheet.setColumnWidth(15, 10 * 512); // 法人代表
		sheet.setColumnWidth(16, 10 * 512); // 法人证件类型
		sheet.setColumnWidth(17, 10 * 512); // 法人证件号
		sheet.setColumnWidth(18, 10 * 512); // 商户联系人姓名
		sheet.setColumnWidth(19, 10 * 512); // 商户联系电话号码
		sheet.setColumnWidth(20, 10 * 512); // 商户注册国家编码
		sheet.setColumnWidth(21, 10 * 512); // 省代码
		sheet.setColumnWidth(22, 10 * 512); // 市代码
		sheet.setColumnWidth(23, 10 * 512); // 县代码
		sheet.setColumnWidth(24, 20 * 512); // 地址
		sheet.setColumnWidth(25, 10 * 512); // 结算账户名称
		sheet.setColumnWidth(26, 10 * 512); // 结算账户
		sheet.setColumnWidth(27, 20 * 512); // 结算账户开户行
		sheet.setColumnWidth(28, 10 * 512); // 结算账户银行联行号
		sheet.setColumnWidth(29, 10 * 512); // 银联终端号
		sheet.setColumnWidth(30, 10 * 512); // 非法人身份证
		sheet.setColumnWidth(31, 10 * 512); // 直属代理商

	}

	@Override
	public HSSFWorkbook exportAgentQueryInfoExcel(List<Object> operList,
			String startDate, String endDate,String invoiceRateFlag) throws ServiceException {
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet( "代理商信息导出" );
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) ("1".equals(invoiceRateFlag) ? 9 : 7 ) );// [(short) 0,(short) 8] 合并 0-8 这几列单元格
			sheet.addMergedRegion(region);
			cell.setCellValue(startDate + "-" + endDate); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中

			String [] coloumValue3 = { "区域" ,"一级代理商", "账号", "姓名 ", "性别", "手机号", "角色类型", "证件号" };

			String [] coloumValue3_data = { "区域" ,"一级代理商", "账号", "姓名 ", "性别", "手机号", "角色类型", "证件号" , "发票扣率", "序号编码" };
			
			
			if ( "1".equals(invoiceRateFlag) ) {
				row = ExcelUtil.createRow(sheet, 1, coloumValue3_data, data_style, true);
				
				for (int i = 0; i < operList.size(); i++) {
					Operators objs = (Operators)operList.get(i);
					String[] col_i = { Constance.Null2EmptyTrim(objs.getCompanyName()),
							Constance.Null2EmptyTrim(objs.getOneagentName()),
							Constance.Null2EmptyTrim(objs.getAccountNo()),
							Constance.Null2EmptyTrim(objs.getRealName()),
							Constance.Null2EmptyTrim(objs.getSex()),
							Constance.Null2EmptyTrim(objs.getMobile()),
							Constance.Null2EmptyTrim(objs.getRoleName()),/*
							Constance.Null2EmptyTrim(objs.getSuperiorName()),*/
							Constance.Null2EmptyTrim(objs.getCardNo()),
							Constance.Null2EmptyTrim(objs.getTaxRegistrationCertificate()),
							Constance.Null2EmptyTrim(objs.getOrganizationCode())};
					
					
					row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
							true);
				}
			}else {
				row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, true);
				
				for (int i = 0; i < operList.size(); i++) {
					Operators objs = (Operators)operList.get(i);
					String[] col_i = { Constance.Null2EmptyTrim(objs.getCompanyName()),
							Constance.Null2EmptyTrim(objs.getOneagentName()),
							Constance.Null2EmptyTrim(objs.getAccountNo()),
							Constance.Null2EmptyTrim(objs.getRealName()),
							Constance.Null2EmptyTrim(objs.getSex()),
							Constance.Null2EmptyTrim(objs.getMobile()),
							Constance.Null2EmptyTrim(objs.getRoleName()),/*
							Constance.Null2EmptyTrim(objs.getSuperiorName()),*/
							Constance.Null2EmptyTrim(objs.getCardNo())};
					
					row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
							true);
				}
			}

		} catch (Exception e) {
			logger.error("########代理商信息导出Excel明细失败######", e);
			throw new ServiceException("代理商信息导出Excel明细失败！");
		}
		return wb;
	}
	
	
	@Override
	public HSSFWorkbook exportSettlementFlow(List<TblTransJnlsSettle> posserList,
			String minDate, String maxDate) throws ServiceException {

		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("结算明细");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("结算明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"结算日期范围", minDate, maxDate, "", ""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			/*String[] coloumValue3 = { "交易日期", "交易时间", "交易类型", "交易结果", "交易账号",
					"卡类型", "交易金额", "商户名", "商户号", "终端号", "流水号", "系统参考号" };*/
			String[] coloumValue3 = { "商户号", "商户名称", "结算金额(元)", "商户手续费(元)", "账号名称",
					"结算银行", "银行账号", "结算日期"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < posserList.size(); i++) {
				TblTransJnlsSettle objs = posserList.get(i);

				//System.out.println("金额：" + objs.getAmountConver());

				
				String[] col_i = { Constance.Null2EmptyTrim(objs.getMchtCd()),
						Constance.Null2EmptyTrim(objs.getMchtName()),
						Constance.Null2EmptyTrim(objs.getMchtSetAmt()),
						Constance.Null2EmptyTrim(objs.getMchtFee()),
						Constance.Null2EmptyTrim(objs.getAccountName()),
						Constance.Null2EmptyTrim(objs.getBankName()),
						Constance.Null2EmptyTrim(objs.getAccount()),
						Constance.Null2EmptyTrim(objs.getSettleDate())};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出结算Excel明细失败######", e);
			throw new ServiceException("导出结算Excel明细失败！");
		}
		return wb;
	}
	
	@Override
	public HSSFWorkbook exportRejectedMerinfoList(List<AuditingItem> auditList,
			String startingDate, String endDate) throws Exception {


		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			HSSFSheet sheet = wb.createSheet("驳回商户详情");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("驳回商户详情"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = startingDate+"-"+endDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"驳回商户日期范围", startingDate, endDate, "", "","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "日期", "商户号", "所属机构", "所属代理商","所属分公司（大区）","一级代理商",
					"商户简称","商户全称", "业务联系人", "业务联系人电话","审核人","审核意见"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < auditList.size(); i++) {
				AuditingItem objs = auditList.get(i);

				//System.out.println("金额：" + objs.getAmountConver());

				
				String[] col_i = { Constance.Null2EmptyTrim(objs.getCreateDate()),
						Constance.Null2EmptyTrim(objs.getObj_Audited()),
						Constance.Null2EmptyTrim(objs.getBankBelongCd()),
						Constance.Null2EmptyTrim(objs.getAgentName()),
						Constance.Null2EmptyTrim(objs.getBranchcompy()),
						Constance.Null2EmptyTrim(objs.getOneagent()),
						Constance.Null2EmptyTrim(objs.getShortName()),
						Constance.Null2EmptyTrim(objs.getMerinfoName()),
						Constance.Null2EmptyTrim(objs.getContanctName()),
						Constance.Null2EmptyTrim(objs.getContanctPh()),
						Constance.Null2EmptyTrim(objs.getAuditorName()),
						Constance.Null2EmptyTrim(objs.getResult_detail())
				};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出驳回商户信息异常######", e);
			throw new ServiceException("导出驳回商户信息失败！");
		}
		return wb;
	
	}

	@Override
	public HSSFWorkbook exportWindControllStaList(
			List<WindControllSta> wcsList, String startingDate, String endDate)
			throws Exception {


		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			HSSFSheet sheet = wb.createSheet("风控审核统计");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 5);
			sheet.addMergedRegion(region);
			cell.setCellValue("风控审核统计"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = startingDate+"-"+endDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"风控审核统计日期范围", startingDate, endDate};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "序号", "操作员账号", "操作员姓名", "领件总数", "审核通过数量",
					"驳回数量"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < wcsList.size(); i++) {
				WindControllSta objs = wcsList.get(i);

				//System.out.println("金额：" + objs.getAmountConver());

				
				String[] col_i = { String.valueOf((i+1)),
						Constance.Null2EmptyTrim(objs.getAccountNo()),
						Constance.Null2EmptyTrim(objs.getRealName()),
						Constance.Null2EmptyTrim(objs.getTotal()),
						Constance.Null2EmptyTrim(objs.getPassCount()),
						Constance.Null2EmptyTrim(objs.getNoPassCount())};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出风控审核统计信息异常######", e);
			throw new ServiceException("导出风控审核统计信息失败！");
		}
		return wb;
	
	}
	
	
	@Override
	public HSSFWorkbook ysbTermianlTransStaticsList(
			List<Object> wcsList, String startingDate, String endDate)
			throws Exception {


		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			HSSFSheet sheet = wb.createSheet("云商宝交易统计");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 5);
			sheet.addMergedRegion(region);
			cell.setCellValue("云商宝交易统计"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = startingDate+"-"+endDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"云商宝交易统计日期范围", startingDate, endDate};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);
			String[] coloumValue3 = { "序号","所属大区", "一级代理商", "交易数量", "交易金额", "交易日期"};
			row = ExcelUtil.createRow(sheet, 3, coloumValue3, data_style, true);
			BigDecimal trantsSum=new BigDecimal("0.0");
			Long trantsNum=(long) 0;
			for (int i = 0; i < wcsList.size(); i++) {
				Object[] objs =  (Object[]) wcsList.get(i);
				String[] col_i = { String.valueOf((i+1)),
						Constance.Null2EmptyTrim(objs[0]),
						Constance.Null2EmptyTrim(objs[1]),
						Constance.Null2EmptyTrim(objs[3]),
						Constance.Null2EmptyTrim(objs[4]),
						Constance.Null2EmptyTrim(objs[2])};

				row = ExcelUtil.createRow(sheet, 4 + i, col_i, general_style,
						true);
				trantsNum=trantsNum+Long.parseLong(String.valueOf(objs[3])) ;
				trantsSum=trantsSum.add((BigDecimal) objs[4]) ;
			}
			String[] coloumValue2 = { "交易总量：",String.valueOf(trantsNum),"","交易总额",String.valueOf(trantsSum)};
			row = ExcelUtil.createRow(sheet,2, coloumValue2, general_style,
					true);
		} catch (Exception e) {
			logger.error("########导出云商宝交易统计信息异常######", e);
			throw new ServiceException("导出云商宝交易统计信息失败！");
		}
		return wb;
	
	}

	@Override
	public SXSSFWorkbook exportTerminalStaList(List<TermBean> termList,
			String startingDate, String endDate,String status) throws Exception {
		SXSSFWorkbook wb = new SXSSFWorkbook();
				try {
					SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("终端激活统计详情");
					CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
							false); // 首行标题样式对象
					CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
							true); // 数据标题样式对象
					CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
							false); // 普通样式对象
					Row row = sheet.createRow(0);
					Font font = wb.createFont();
					Cell cell = row.createCell((short) 0);
					CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
							(short) 4);
					sheet.addMergedRegion(region);
					cell.setCellValue("终端激活统计详情"); // 表格的第一行第一列显示的数据
					cell.setCellStyle(top_style); // 样式，居中
					String dateStr = startingDate+"-"+endDate;
					String[] coloumValue1 = { "报表日期：", Constance.formateDate(),
							"激活日期范围", startingDate, endDate};
					row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
							true);
					String[] totalValue = { "共计", String.valueOf(termList.size())+"台", "", "", ""};
					row = ExcelUtil.createRow(sheet, 2, totalValue, data_style, true);
					String name1="分公司",name2="直属代理商";
					if("exist".equals(status))
					{
						name1="代理商";name2="直属子代理";
					}
					String[] coloumValue3 = { name1, name2, "商户号", "终端号", "SN序列号"};
					row = ExcelUtil.createRow(sheet, 3, coloumValue3, data_style, true);

					for (int i = 0; i < termList.size(); i++) {
						TermBean objs = termList.get(i);

						String[] col_i = { Constance.Null2EmptyTrim(objs.getOrgName()),
								Constance.Null2EmptyTrim(objs.getAgentName()),
								Constance.Null2EmptyTrim(objs.getMid()),
								Constance.Null2EmptyTrim(objs.getTid()),
								Constance.Null2EmptyTrim(objs.getSerialNum())};

						row = ExcelUtil.createRow(sheet, 4 + i, col_i, general_style,
								true);
					}
					//数据统计
					SXSSFSheet sheet_data = (SXSSFSheet) wb.createSheet("终端激活统计数据");
					Row row_data = sheet_data.createRow(0);
					Cell cell_data = row_data.createCell((short) 0);
					CellRangeAddress region_data = new CellRangeAddress((short) 0, (short) 0, (short) 0,
							(short) 4);
					sheet_data.addMergedRegion(region_data);
					cell_data.setCellValue("终端激活统计数据"); // 表格的第一行第一列显示的数据
					cell_data.setCellStyle(top_style); // 样式，居中
					String[] coloumValueTitle = {"激活日期范围", startingDate, endDate};
					row = ExcelUtil.createRow(sheet_data, 1, coloumValueTitle, general_style,
							true);
					//所属分工公司
					Set<String> setCompany=new HashSet<String>();
					List<TermBean> listBeans=new ArrayList<TermBean>();
					for(int i = 0; i < termList.size(); i++)
					{
						TermBean tb=termList.get(i);
						setCompany.add(tb.getOrgName());
					}
					Iterator<String> it=setCompany.iterator();
					int rows=1;
					String[] totalValueData = {name1,"台",""};
					row = ExcelUtil.createRow(sheet_data, ++rows, totalValueData, data_style, true);
					while(it.hasNext())
					{
						rows++;
						int totalCount=0;
						String orgName=it.next();
						Set<String> subCompany=new HashSet<String>();
						for(int i = 0; i < termList.size(); i++)
						{
							TermBean tb=termList.get(i);
							if(orgName.equals(tb.getOrgName()))
							{
								subCompany.add(tb.getAgentName());
								totalCount++;
							}
						}
						String[] totalValueData_ = {orgName,String.valueOf(totalCount),""};
						row = ExcelUtil.createRow(sheet_data, rows, totalValueData_, general_style, true);
						Iterator<String> itSub=subCompany.iterator();
						while(itSub.hasNext())
						{
							String agentName=itSub.next();
							TermBean tb=new TermBean();
							tb.setOrgName(orgName);
							tb.setAgentName(agentName);
							listBeans.add(tb);
						}
					}
					//插入数据
					String[] totalValueDataDetail = {name1,name2,"台"};
					row = ExcelUtil.createRow(sheet_data, ++rows, totalValueDataDetail, data_style, true);
					for(int i = 0; i < listBeans.size(); i++)
					{	
						rows++;
						int simpleCount=0;
						String orgName=listBeans.get(i).getOrgName();
						String agentName=listBeans.get(i).getAgentName();
						for(int j = 0; j < termList.size(); j++)
						{
							if(orgName.equals(termList.get(j).getOrgName())&&agentName.equals(termList.get(j).getAgentName()))
							{
									simpleCount++;
							}
						}
						String[] totalValueDataDetail_ = { orgName, agentName, String.valueOf(simpleCount)};
						row = ExcelUtil.createRow(sheet_data, rows, totalValueDataDetail_, general_style, true);
					}
				} catch (Exception e) {
					logger.error("########导出终端激活统计Excel明细失败######", e);
					throw new ServiceException("导出终端激活统计Excel明细失败！");
				}
				return wb;
	}
	
	
	@Override
	public HSSFWorkbook exportNoImportedMerList(Map<String,Object> resultMap)
			throws Exception {

		List<List<Object>> noImportList = (List<List<Object>>) resultMap.get("noImportedList");
		
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			HSSFSheet sheet = wb.createSheet("成功导入商户统计");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 42);
			sheet.addMergedRegion(region);
			cell.setCellValue("成功导入商户统计"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "成功导入总数：",String.valueOf(noImportList.size()),"","","","","",""
					,"","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "机构号", "商户号", "装机地址市/县", "MCC42域",
					"商户工商注册名称", "营业名称", "营业执照号码", "经营范围", "主营业务", "法人代表",
					"法人证件类型", "法人代表证件号", "身份证有效起始日期","身份证有效结束日期", "商户联系人", "商户联系人电话",
					"营业起始时间", "营业终止时间", "商户注册地址省地址", "商户注册地址地市地址",
					"商户注册地址区县地址", "商户注册地址", "商户类型", "商户直间连", "特殊计费类型",
					"特殊计费档次", "结算账户类型", "是否法人结算", "非法人身份证号", "结算账户名称",
					"结算账户账号", "结算账户开户行名称", "是否特殊费率商户", "开通T1", "T1借记卡费率(%)",
					"T1借记卡封顶", "T1贷记卡费率(%)", "开通T0", "T0借记卡费率(%)",
					"T0贷记卡费率(%)", "余额查询", "消费撤销","消费冲正", "开通终端数量","开通终端信息" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < noImportList.size(); i++) {
				List<Object> objs = noImportList.get(i);

				//System.out.println("金额：" + objs.getAmountConver());

				
				String[] col_i = { Constance.Null2EmptyTrim(objs.get(0)),
						Constance.Null2EmptyTrim(objs.get(1)),
						Constance.Null2EmptyTrim(objs.get(2)),
						Constance.Null2EmptyTrim(objs.get(3)),
						Constance.Null2EmptyTrim(objs.get(4)),
						Constance.Null2EmptyTrim(objs.get(5)),
						Constance.Null2EmptyTrim(objs.get(6)),
						Constance.Null2EmptyTrim(objs.get(7)),
						Constance.Null2EmptyTrim(objs.get(8)),
						Constance.Null2EmptyTrim(objs.get(9)),
						Constance.Null2EmptyTrim(objs.get(10)),
						Constance.Null2EmptyTrim(objs.get(11)),
						Constance.Null2EmptyTrim(objs.get(12)),
						Constance.Null2EmptyTrim(objs.get(13)),
						Constance.Null2EmptyTrim(objs.get(14)),
						Constance.Null2EmptyTrim(objs.get(15)),
						Constance.Null2EmptyTrim(objs.get(16)),
						Constance.Null2EmptyTrim(objs.get(17)),
						Constance.Null2EmptyTrim(objs.get(18)),
						Constance.Null2EmptyTrim(objs.get(19)),
						Constance.Null2EmptyTrim(objs.get(20)),
						Constance.Null2EmptyTrim(objs.get(21)),
						Constance.Null2EmptyTrim(objs.get(22)),
						Constance.Null2EmptyTrim(objs.get(23)),
						Constance.Null2EmptyTrim(objs.get(24)),
						Constance.Null2EmptyTrim(objs.get(25)),
						Constance.Null2EmptyTrim(objs.get(26)),
						Constance.Null2EmptyTrim(objs.get(27)),
						Constance.Null2EmptyTrim(objs.get(28)),
						Constance.Null2EmptyTrim(objs.get(29)),
						Constance.Null2EmptyTrim(objs.get(30)),
						Constance.Null2EmptyTrim(objs.get(31)),
						Constance.Null2EmptyTrim(objs.get(32)),
						Constance.Null2EmptyTrim(objs.get(33)),
						Constance.Null2EmptyTrim(objs.get(34)),
						Constance.Null2EmptyTrim(objs.get(35)),
						Constance.Null2EmptyTrim(objs.get(36)),
						Constance.Null2EmptyTrim(objs.get(37)),
						Constance.Null2EmptyTrim(objs.get(38)),
						Constance.Null2EmptyTrim(objs.get(39)),
						Constance.Null2EmptyTrim(objs.get(40)),
						Constance.Null2EmptyTrim(objs.get(41)),
						Constance.Null2EmptyTrim(objs.get(42)),
						Constance.Null2EmptyTrim(objs.get(43)),
						Constance.Null2EmptyTrim(objs.get(44))
						
						};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出成功导入商户信息失败######", e);
			throw new ServiceException("导出成功导入商户信息失败！");
		}
		return wb;
	
	}

	@Override
	public SXSSFWorkbook exportHisTransFlow(List<TblTfrHisTrnLog> tftList,
			String minDate, String maxDate, Map<String, String> resMap,Operators op) throws Exception {

		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("历史交易明细");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 13);
			sheet.addMergedRegion(region);
			cell.setCellValue("历史交易明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			/*String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"交易日期范围", minDate, maxDate, "", "交易成功总金额", resMap.get("totalAmt"), "", "交易成功总笔数", resMap.get("totalCount"),"","",""};*/
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(),
					"交易日期范围", minDate, maxDate,"交易成功总金额", resMap.get("totalAmt"),"交易成功总笔数", resMap.get("totalCount"), "撤销成功总金额", resMap.get("cxTotalAmt"),"撤销成功总笔数",resMap.get("cxTotalCount"),"","","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			/*String[] coloumValue3 = { "交易日期", "交易时间", "交易类型", "交易结果", "是否被处理","交易账号",
					"卡类型", "交易金额", "商户名", "商户号", "终端号", "流水号", "系统参考号" };*/
			if("03".equals(op.getRoleType())){
				String[] coloumValue3 = { "商户号", "商户名", "终端号", "交易账号", "交易金额(元)",
						"交易类型", "银联返回码", "交易结果", "交易状态","交易日期", "交易时间", "卡类型","接触方式", "流水号", "系统参考号","系统跟踪号","交易参考号"};
				row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			}else{
				String[] coloumValue3 = { "商户号", "商户名", "终端号", "交易账号", "交易金额(元)",
						"交易类型", "银联返回码", "交易结果", "交易状态","交易日期", "交易时间", "卡类型","接触方式", "流水号", "系统参考号","系统跟踪号","交易参考号","银联商户号" };
				row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			}
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < tftList.size(); i++) {
				TblTfrHisTrnLog objs = tftList.get(i);

				

				/*String[] col_i = { Constance.Null2EmptyTrim(objs.getTransDt()),
						Constance.Null2EmptyTrim(objs.getTransMt()),
						Constance.Null2EmptyTrim(objs.getTransInf()),
						Constance.Null2EmptyTrim(objs.getRespName()),
						Constance.Null2EmptyTrim(objs.getPriAcctNo()),
						Constance.Null2EmptyTrim(objs.getCardType()),
						Constance.isMoney(objs.getAmountConver()),
						Constance.Null2EmptyTrim(objs.getMchntNmConv()),
						Constance.Null2EmptyTrim(objs.getMchntCd()),
						Constance.Null2EmptyTrim(objs.getTermId()),
						Constance.Null2EmptyTrim(objs.getMaTransSeq()),
						Constance.Null2EmptyTrim(objs.getRetriRefNoConv()) };*/
				if("03".equals(op.getRoleType())){
					String[] col_i = { Constance.Null2EmptyTrim(objs.getMchntCd()),
							Constance.Null2EmptyTrim(objs.getMchntNmConv()),
							Constance.Null2EmptyTrim(objs.getTermId()),
							Constance.Null2EmptyTrim(objs.getPriAcctNo()),
							Constance.Null2EmptyTrim(objs.getTransAt()),
							Constance.Null2EmptyTrim(objs.getTransInf()),
							Constance.Null2EmptyTrim(objs.getRespCd()),
							Constance.Null2EmptyTrim(objs.getRespName()),
							Constance.Null2EmptyTrim(objs.getDealRs()),
							Constance.Null2EmptyTrim(objs.getTransDt()),
							Constance.Null2EmptyTrim(objs.getTransMt()),
							Constance.Null2EmptyTrim(objs.getCardType()),
							Constance.Null2EmptyTrim(objs.getPosEntryMdCdName()),
							Constance.Null2EmptyTrim(objs.getMaTransSeq()),
							Constance.Null2EmptyTrim(objs.getKeyRsp()),
							Constance.Null2EmptyTrim(objs.getRetriRefNo()),
							Constance.Null2EmptyTrim(objs.getRetriRefNoConv())
							};
					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
				}else{
					String[] col_i = { Constance.Null2EmptyTrim(objs.getMchntCd()),
							Constance.Null2EmptyTrim(objs.getMchntNmConv()),
							Constance.Null2EmptyTrim(objs.getTermId()),
							Constance.Null2EmptyTrim(objs.getPriAcctNo()),
							Constance.Null2EmptyTrim(objs.getTransAt()),
							Constance.Null2EmptyTrim(objs.getTransInf()),
							Constance.Null2EmptyTrim(objs.getRespCd()),
							Constance.Null2EmptyTrim(objs.getRespName()),
							Constance.Null2EmptyTrim(objs.getDealRs()),
							Constance.Null2EmptyTrim(objs.getTransDt()),
							Constance.Null2EmptyTrim(objs.getTransMt()),
							Constance.Null2EmptyTrim(objs.getCardType()),
							Constance.Null2EmptyTrim(objs.getPosEntryMdCdName()),
							Constance.Null2EmptyTrim(objs.getMaTransSeq()),
							Constance.Null2EmptyTrim(objs.getKeyRsp()),
							Constance.Null2EmptyTrim(objs.getRetriRefNo()),
							Constance.Null2EmptyTrim(objs.getRetriRefNoConv()),
							Constance.Null2EmptyTrim(objs.getMchntCdConv())
							};
					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
				}
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出交易Excel明细失败######", e);
			throw new ServiceException("导出历史交易Excel明细失败！");
		}
		return wb;
	}
	
	@Override
	public SXSSFWorkbook exportHisRiskTransFlow(List<TblTfrHisTrnLog> tftList,
			String minDate, String maxDate) throws Exception {

		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("历史交易明细");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 14);
			sheet.addMergedRegion(region);
			cell.setCellValue("历史交易明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			/*String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"交易日期范围", minDate, maxDate, "", "交易成功总金额", resMap.get("totalAmt"), "", "交易成功总笔数", resMap.get("totalCount"),"","",""};*/
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(),
					"交易日期范围", minDate, maxDate,"", "","", "", "", "","","","","","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			/*String[] coloumValue3 = { "交易日期", "交易时间", "交易类型", "交易结果", "是否被处理","交易账号",
					"卡类型", "交易金额", "商户名", "商户号", "终端号", "流水号", "系统参考号" };*/
			String[] coloumValue3 = { "商户号", "商户名", "终端号", "交易账号", "交易金额(元)",
					"交易类型","返回码", "交易结果", "交易状态","交易日期", "交易时间", "卡类型","是否四要素验证","接触方式", "流水号", "系统参考号","系统跟踪号","交易参考号","银联商户号" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < tftList.size(); i++) {
				TblTfrHisTrnLog objs = tftList.get(i);

				

				/*String[] col_i = { Constance.Null2EmptyTrim(objs.getTransDt()),
						Constance.Null2EmptyTrim(objs.getTransMt()),
						Constance.Null2EmptyTrim(objs.getTransInf()),
						Constance.Null2EmptyTrim(objs.getRespName()),
						Constance.Null2EmptyTrim(objs.getPriAcctNo()),
						Constance.Null2EmptyTrim(objs.getCardType()),
						Constance.isMoney(objs.getAmountConver()),
						Constance.Null2EmptyTrim(objs.getMchntNmConv()),
						Constance.Null2EmptyTrim(objs.getMchntCd()),
						Constance.Null2EmptyTrim(objs.getTermId()),
						Constance.Null2EmptyTrim(objs.getMaTransSeq()),
						Constance.Null2EmptyTrim(objs.getRetriRefNoConv()) };*/
				String[] col_i = { Constance.Null2EmptyTrim(objs.getMchntCd()),
						Constance.Null2EmptyTrim(objs.getMchntNmConv()),
						Constance.Null2EmptyTrim(objs.getTermId()),
						Constance.Null2EmptyTrim(objs.getPriAcctNo()),
						Constance.Null2EmptyTrim(objs.getTransAt()),
						Constance.Null2EmptyTrim(objs.getTransInf()),
						Constance.Null2EmptyTrim(objs.getRespCd()),
						Constance.Null2EmptyTrim(objs.getRespName()),
						Constance.Null2EmptyTrim(objs.getDealRs()),
						Constance.Null2EmptyTrim(objs.getTransDt()),
						Constance.Null2EmptyTrim(objs.getTransMt()),
						Constance.Null2EmptyTrim(objs.getCardType()),
						Constance.Null2EmptyTrim(objs.getIsFourElements()),
						Constance.Null2EmptyTrim(objs.getPosEntryMdCdName()),
						Constance.Null2EmptyTrim(objs.getMaTransSeq()),
						Constance.Null2EmptyTrim(objs.getKeyRsp()),
						Constance.Null2EmptyTrim(objs.getRetriRefNo()),
						Constance.Null2EmptyTrim(objs.getRetriRefNoConv()),
						Constance.Null2EmptyTrim(objs.getMchntCdConv())};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出交易Excel明细失败######", e);
			throw new ServiceException("导出历史交易Excel明细失败！");
		}
		return wb;
	}

	@Override
	public SXSSFWorkbook exportTransStastics(List<TblTfrTrnLog> tftList,
			String minDate, String maxDate, Map<String, String> summaryMap) throws Exception{
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("交易统计详情");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 6);
			sheet.addMergedRegion(region);
			cell.setCellValue("交易统计详情"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"交易日期范围", minDate, maxDate, ""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);
			
			String[] coloumValue2 = { "T0交易总额", summaryMap.get("tzAmt"), "", "T1交易总额",summaryMap.get("toAmt") , "",""};
			row = ExcelUtil.createRow(sheet, 2, coloumValue2, general_style,
					true);

			String[] coloumValue3 = { "区域", "一级代理商", "交易金额（元）", "交易类型", "交易日期",
					"卡类型", "接触方式" };
			row = ExcelUtil.createRow(sheet, 3, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < tftList.size(); i++) {
				TblTfrTrnLog objs = tftList.get(i);

				String[] col_i = { Constance.Null2EmptyTrim(objs.getCompanyName()),
						Constance.Null2EmptyTrim(objs.getOneAgentName()),
						Constance.Null2EmptyTrim(objs.getTransAt()),
						Constance.Null2EmptyTrim(objs.getTransInf()),
						Constance.Null2EmptyTrim(objs.getTransDt()),
						Constance.Null2EmptyTrim(objs.getCardType()),
						Constance.Null2EmptyTrim(objs.getPosEntryMdCdName()) };

				row = ExcelUtil.createRow(sheet, 4 + i, col_i, general_style,
						true);
				
			}

		} catch (Exception e) {
			logger.error("########导出交易Excel明细失败######", e);
			throw new ServiceException("导出历史交易Excel明细失败！");
		}
		return wb;
	}
    /**
     * 按时间导出商户变更结算信息	
     */
    @Override
    public HSSFWorkbook exportMerinfoChangeC2Info(List<MerinfoChange> merinfoChagenList, String startDate,
            String endDate) throws ServiceException {
        HSSFWorkbook wb = new HSSFWorkbook();
        try {
                HSSFSheet sheet = wb.createSheet( "变更审核通过后结算信息导出" );
                setStatMerColWidth(sheet); // 设置每列宽度
                
                HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
                                false); // 首行标题样式对象
                HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
                                true); // 数据标题样式对象
                HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
                                false); // 普通样式对象
                general_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中    
                
                general_style.setWrapText(true);//设置自动换行
                HSSFRow row = sheet.createRow(0);
                HSSFFont font = wb.createFont();
                HSSFCell cell = row.createCell((short) 0);
                Region region = new Region((short) 0, (short) 0, (short) 0,
                                (short) 10);
                sheet.addMergedRegion(region);
                cell.setCellValue("变更审核通过后结算信息[ "+startDate + "-" + endDate+" ]"); // 表格的第一行第一列显示的数据
                cell.setCellStyle(top_style); // 样式，居中
                
                String[] coloumValue3 = {"变更审核通过时间","商户号", "商户简称", "账户名称", "开户行", "账户账号", "银联号", "对公对私","直属代理商","一级代理商","所属大区"};
                row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, false); // widthFlag : true 设置宽度, false 不设置宽度
                Double allCount1 = 0.0;// 总数量
                Double allCount2 = 0.0;// 通过总数量
                Double allCount3 = 0.0;// 退回总数量
                for (int i = 0; i < merinfoChagenList.size(); i++) {
                        MerinfoChange merinfoChange = merinfoChagenList.get(i);
                        String _pubOrPri = merinfoChange.getPubOrPri();
                        
                        String pubOrPri= null;
                        if(null != _pubOrPri && _pubOrPri.equals("1")){
                            pubOrPri="对公（法人）";
                        }else{
                           pubOrPri="对私（非法人）"; 
                        }
                        
                        String[] col_i = { 
                                        // 结算信息 ---------->结算银行 联行号     结算银行账号名称        结算银行账号
                                        Constance.Null2EmptyTrim(DateUtil.initStartDate(merinfoChange.getDtAuditorTime())),
                                        Constance.Null2EmptyTrim(merinfoChange.getMid()),
                                        Constance.Null2EmptyTrim(merinfoChange.getNameBusi()),
                                        Constance.Null2EmptyTrim(merinfoChange.getBankAccountName()),
                                        Constance.Null2EmptyTrim(merinfoChange.getOpenBank()),
                                        Constance.Null2EmptyTrim(merinfoChange.getBankAccount()),
                                        Constance.Null2EmptyTrim(merinfoChange.getBankCode()),
                                        Constance.Null2EmptyTrim(pubOrPri),
                                        Constance.Null2EmptyTrim(merinfoChange.getUrl1()),
                                        Constance.Null2EmptyTrim(merinfoChange.getUrl2()),
                                        Constance.Null2EmptyTrim(merinfoChange.getUrl3())
                                        
                        };
                        row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
                                        false);
                }
        } catch (Exception e) {
                logger.error("########商户变更结算信息导出Excel明细失败######", e);
                throw new ServiceException("商户变更结算信息导出Excel明细失败！");
        }
        return wb;
    }
	
	
    /**
     * 导出代理商信息匹配
     */
	@Override
	public HSSFWorkbook exportAgencyExcel(Map<String,Object> map)throws ServiceException {
		// TODO Auto-generated method stub
		
			HSSFWorkbook wb = new HSSFWorkbook();
			try {
				Map<String, String> respCodes  =  new LinkedHashMap<String, String>();
				respCodes= ConfigProperties
						.getRespCodeQueryTypes();
				HSSFSheet sheet = wb.createSheet("代理商信息");
				
				 HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
                         false); // 首行标题样式对象
				HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
						true); // 数据标题样式对象
				HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
						false); // 普通样式对象
				HSSFRow row = sheet.createRow(0);
				HSSFFont font = wb.createFont();//设置字体
				HSSFCell cell = row.createCell((short) 0);//创建单元格
				Region region = new Region((short) 0, (short) 0, (short) 0,(short) 3);
				sheet.addMergedRegion(region);
				cell.setCellValue("代理商信息"); // 表格的第一行第一列显示的数据
				cell.setCellStyle(top_style); // 样式，居中	
				String[] coloumValue3 = { "商户号","商户简称","商户名称", "所属区域", "所属机构"};
				setStatMerColWidth(sheet);
				row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, true);//
				List<List<Object>> exportList ;
				if(map.containsKey("data"))
				{
					exportList = (List<List<Object>>) map.get("data");
				}else{
					exportList = new ArrayList<List<Object>>();
				}
				for (int i = 0; i < exportList.size(); i++) {
					List<Object> row_data = exportList.get(i);
					Object[] rowarr = row_data.toArray();
					String[] col_i = new String[rowarr.length];
					for(int j = 0; j< rowarr.length; j++)
					{
						col_i[j] = Constance.Null2EmptyTrim(rowarr[j]);
					}
					row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,true);
					
					}
				
				if(map.containsKey("result"))
				{
					String result = (String) map.get("result");
					String[] coloumValue = { "汇总：", Constance.Null2EmptyTrim(result), "" };
					row = ExcelUtil.createRow(sheet, exportList.size() + 3, coloumValue,
							general_style, true);
				}
			} catch (Exception e) {
				logger.error("########导出信息匹配Excel失败######", e);
				throw new ServiceException("导出信息匹配Excel失败！");
			}
			return wb;
		}

	@Override
	public HSSFWorkbook exportmerinfoList(List<MerinfoSearchBean> merfList)throws Exception {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("商户查询明细");
			setExportMerinfoColWidth(sheet);
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
                    false); // 首行标题样式对象
            HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
                true); // 数据标题样式对象
            HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
                false); // 普通样式对象
            general_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中    

            general_style.setWrapText(true);//设置自动换行
            HSSFRow row = sheet.createRow(0);
            HSSFFont font = wb.createFont();
            HSSFCell cell = row.createCell((short) 0);
            Region region = new Region((short) 0, (short) 0, (short) 0,(short) 10);
             sheet.addMergedRegion(region);
			cell.setCellValue("商户查询明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			cell.setCellStyle(general_style); 
			//general_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中    
            general_style.setWrapText(true);//设置自动换行
			String[] coloumValue3 = { "商户号", "商户简称", "营业名称", "结算方式", "商户类型",
					"所属机构", "所属分公司", "直属代理商","审核人员", "审核日期", "状态","业务联系人", "业务联系人电话", "法人","法人证件号","结算银行",
					"联行号","结算银行账号名称","结算银行账号","商户地址"};
			row = ExcelUtil.createRow(sheet, 1, coloumValue3, (HSSFCellStyle) data_style, false);
			

			for (int i = 0; i < merfList.size(); i++) {
				MerinfoSearchBean objs = merfList.get(i);
				List list = new ArrayList<String>();
				String ss =null;
				for (String obj : objs.getMerCycle() ) {
				    	list.add(obj);
				    	
				    	if(list.size()>1){
				    		ss= (String) list.get(0)+list.get(1);
				    	}else{
				    		ss=(String) list.get(0);
				    		ss=ss.replace("+", "");
				    	}
				};
				String[] col_i = { 
						Constance.Null2EmptyTrim(objs.getMid()),
						Constance.Null2EmptyTrim(objs.getMerName()),
						Constance.Null2EmptyTrim(objs.getMerBrName()),
						Constance.Null2EmptyTrim(ss),
						Constance.Null2EmptyTrim(objs.getNationCd()),
						Constance.Null2EmptyTrim(objs.getMerKind()),
						Constance.Null2EmptyTrim(objs.getCompanyName()),
						Constance.Null2EmptyTrim(objs.getAgentId()),
						Constance.Null2EmptyTrim(objs.getBus_lic()),
						Constance.Null2EmptyTrim(objs.getApplDate()),
						Constance.Null2EmptyTrim(objs.getFlag()),
						Constance.Null2EmptyTrim(objs.getPerName()),
						Constance.Null2EmptyTrim(objs.getPerPhone()),
						Constance.Null2EmptyTrim(objs.getInterContact()),
						Constance.Null2EmptyTrim(objs.getCardNo()),
						Constance.Null2EmptyTrim(objs.getAmount1()),
						Constance.Null2EmptyTrim(objs.getAmount2()),
						Constance.Null2EmptyTrim(objs.getRsvl()),
						Constance.Null2EmptyTrim(objs.getRsvl3()),
						Constance.Null2EmptyTrim(objs.getAddr()),
						
				};
				if ("0".equals(col_i[4])) {
					col_i[4] = "个体户";
				}else if("1".equals(col_i[4])){
					col_i[4] = "企业用户";
				}	
				row = ExcelUtil.createRow(sheet, 2 + i, col_i, (HSSFCellStyle) general_style,false);
			}

		} catch (Exception e) {
			logger.error("########导出商户Excel明细失败######", e);
			throw new ServiceException("导出商户Excel明细失败！");
		}
		return wb;
	}
	public void setExportMerinfoColWidth(HSSFSheet sheet){
		sheet.setColumnWidth(0, 10 * 512); // 商户号
		sheet.setColumnWidth(1, 10 * 512); // 商户简称
		sheet.setColumnWidth(2, 10 * 512); // 营业名称
		sheet.setColumnWidth(3, 10 * 512); // 结算方式
		sheet.setColumnWidth(4, 10 * 512); // 商户类型
		sheet.setColumnWidth(5, 10 * 512); // 所属机构
		sheet.setColumnWidth(6, 10 * 512); // 所属分公司
		sheet.setColumnWidth(7, 10 * 512); // 直属代理商
		sheet.setColumnWidth(8, 10 * 512); // 审核人员
		sheet.setColumnWidth(9, 10 * 512); // 审核日期
		sheet.setColumnWidth(10, 10 * 512); // 状态
		sheet.setColumnWidth(11, 10 * 512); // 业务联系人
		sheet.setColumnWidth(12, 10 * 512); // 业务联系人电话
		sheet.setColumnWidth(13, 10 * 512); // 法人
		sheet.setColumnWidth(14, 15 * 512); // 法人证件号
		sheet.setColumnWidth(15, 25 * 512); // 结算银行
		sheet.setColumnWidth(16, 15 * 512); // 联行号
		sheet.setColumnWidth(17, 10 * 512); // 结算银行账户名称
		sheet.setColumnWidth(18, 15 * 512); // 结算银行账号
		sheet.setColumnWidth(19, 15 * 512); // 商户地址
	}
	


	@Override
	public SXSSFWorkbook exportTerminalSnBatchStaList(List<TblTermBrandStatistics> termList,Map<String, Map<String, String>> snStaInfoMap , 
			String startingDate, String endDate,String status,Operators logingMerchant) throws Exception {
			SXSSFWorkbook wb = new SXSSFWorkbook();
			Map<String, Map<String, String>> snStaMap = new HashMap<String, Map<String, String>>();
			try {
					SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("终端sn号段绑定详情");
					setTerminalStaColWidth(sheet); // 设置每列宽度
					CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
							false); // 首行标题样式对象
					CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
							true); // 数据标题样式对象
					CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
							false); // 普通样式对象
					Row row = sheet.createRow(0);
					Font font = wb.createFont();
					Cell cell = row.createCell((short) 0);
					CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
							(short) 7);
					sheet.addMergedRegion(region);
					cell.setCellValue(Constance.formateDate()); // 表格的第一行第一列显示的数据
					cell.setCellStyle(top_style); // 样式，居中
					
					String[] coloumValue3 = { "所属分公司", "所属一级代理商", "代理商", "SN号", "品牌", "绑定日期", "终端号", "商户号"};
					row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, true);

			/*		Double allCount1 = 0.0;// 总数量
					Double allCount2 = 0.0;// 激活总数量
					Double allCount3 = 0.0;// 未激活总数量
			*/		
			//		Map<String, Map<String, String>> infoMap = new HashMap<String, Map<String, String>>();
					
					for (int i = 0; i < termList.size(); i++) {
						TblTermBrandStatistics objs = termList.get(i);

						String agentName = Constance.Null2EmptyTrim( objs.getAgentName() );
						String companyName = Constance.Null2EmptyTrim( objs.getCompanyName() ) ;
						String oneAgentName = Constance.Null2EmptyTrim( objs.getOneagentName() );
						String[] col_i = { 
								companyName , // 所属大区[分公司] 名称
								oneAgentName , // 所属一级代理商 名称
								agentName , // 代理商名称
								Constance.Null2EmptyTrim( objs.getSerialNum() ) , // SN号
								Constance.Null2EmptyTrim( objs.getPosBrand() ) , // 品牌
								Constance.Null2EmptyTrim( objs.getBrandDateStr() ) , // 绑定日期
								Constance.Null2EmptyTrim( objs.getTermId() ) , // 终端号
								Constance.Null2EmptyTrim( objs.getMchntCd() )  // 商户号
								};
						row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
								true);
						
						String oneAgentId = String.valueOf( objs.getOneagentId() );
						if ( 0 >= snStaMap.size() ||  !snStaMap.containsKey(oneAgentId) ){

							Map<String, String> contextMap = new HashMap<String, String>();
							contextMap.put("count", "1" );
							contextMap.put("companyName", companyName);
							contextMap.put("oneAgentName", oneAgentName);
							
							snStaMap.put( oneAgentId ,  (contextMap) );
						}else {
							Map<String, String> contextMap = snStaMap.get(oneAgentId);
							Integer count = (Integer.parseInt(contextMap.get("count")) + 1);
							contextMap.put("count", String.valueOf(count));
							
							snStaMap.put( oneAgentId ,  (contextMap) );
							
						}
/*
						String id = String.valueOf(objs.getAgentId());

						if( snStaMap.containsKey(id) ) {

							Map<String, String> contextMap = snStaMap.get(id);
							Integer count = (Integer.parseInt(contextMap.get("count")) + 1);
							contextMap.put("count", String.valueOf(count));
							snStaMap.put( id ,  (contextMap) );
						}*/
					}
					// 登录账号为 管理员 或者 收单事业部,才有权限 导出统计信息
					if(logingMerchant.getRoleType().equals(Role.SYSTEM.getCode()) 
							|| Constant.SHOUDANSHIYEBU_ID.equals( String.valueOf(logingMerchant.getId()) ) ) {
	
						SXSSFSheet sheet_data = (SXSSFSheet) wb.createSheet("终端sn号段绑定统计");
						Row row_data = sheet_data.createRow(0);
						Cell cell_data = row_data.createCell((short) 0);
						CellRangeAddress region_data = new CellRangeAddress((short) 0, (short) 0, (short) 0,
								(short) 2);
						sheet_data.addMergedRegion(region_data);
						cell_data.setCellValue(Constance.formateDate()); // 表格的第一行第一列显示的数据
						cell_data.setCellStyle(top_style); // 样式，居中
					
						
						String[] coloumValue3_data = {  "所属分公司","所属一级代理商", "绑定数量" };
						row_data = ExcelUtil.createRow(sheet_data, 1, coloumValue3_data, data_style, true);
	
						Integer brandCounts = 0;// 绑定数量
					//	Integer unBrandCounts = 0;// 绑定数量
					//	Integer allBrandNums = 0;// 总数量
						
						int s = 0;
						for (String key : snStaMap.keySet()) {
							Map<String, String> contextMap = snStaMap.get(key);
						//	Integer allNum = Integer.parseInt( contextMap.get("allNum") );
							Integer brandNum = Integer.parseInt( contextMap.get("count") );
						//	Integer unBrandCount = allNum - brandNum ;
							
							String[] col_i = { contextMap.get("companyName"), 
									contextMap.get("oneAgentName"),
							//		contextMap.get("agentName") , 
									String.valueOf( brandNum ) //, 
						//			String.valueOf( unBrandCount ) , 
						//			String.valueOf( allNum ) 
									};
							row_data = ExcelUtil.createRow(sheet_data, 2 + s, col_i, general_style, true);
							brandCounts = brandCounts + brandNum ;
					//		unBrandCounts = unBrandCounts + unBrandCount ;
					//		allBrandNums = allBrandNums + allNum ;
							s++;
						}
						
						DecimalFormat df = new DecimalFormat("0");
						String[] coloumValue_data = { "总计：", "", df.format(brandCounts)};
						row_data = ExcelUtil.createRow(sheet_data, snStaMap.size() + 2, coloumValue_data,
								general_style, true);
						
					}
				} catch (Exception e) {
					logger.error("########导出终端sn号段绑定统计Excel明细失败######", e);
					throw new ServiceException("导出终端sn号段绑定统计Excel明细失败！");
				}
				return wb;
	}

	public void setTerminalStaColWidth(SXSSFSheet sheet){

		sheet.setColumnWidth(0, 20 * 512); // 一级代理商
		sheet.setColumnWidth(1, 10 * 512); // 激活数量
		sheet.setColumnWidth(2, 10 * 512); // 未激活数量
		sheet.setColumnWidth(3, 10 * 512); // 总数量

	}
	
	@Override
	public SXSSFWorkbook exportFreezeMcht(List<FreezeBean> freezeList)
			throws Exception {
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("强扣登记");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("强扣登记"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValueTitle = {"序号","商户号", "需要冻结金额（元）", "已冻结金额（元）", "未冻结金额（元）", "是否启用",
					"是否冻满", "备注","原调单流水","原调单流水交易卡号","日期","操作账号"};
			String[] coloumValueTitle2 = {"明细序号","终端号","收单商户名","多冻结金额（元）", "还需冻结金额（元）", "本次冻结金额（元）", "交易日期",
					"交易时间", "清算日期","交易提交时间","收款行行号","收款行行名","收款人帐号","收款人名称","付款行行号","付款人帐号","金额","收单手续费","T0手续费","结算金额"};
			row = ExcelUtil.createRow(sheet, 1, coloumValueTitle,data_style, true);
			row = ExcelUtil.createRow(sheet, 2, coloumValueTitle2,general_style, true);
			int counts=0;
			for (int i = 0; i < freezeList.size(); i++) {
				FreezeBean bean =  freezeList.get(i);
				String[] col_i = { 
						Constance.Null2EmptyTrim(i+1),
						Constance.Null2EmptyTrim(bean.getMchntCd()),
						Constance.Null2EmptyTrim(bean.getFreezeAmt()),
						Constance.Null2EmptyTrim(bean.getFreezeHadSum()),
						Constance.Null2EmptyTrim(bean.getFreezeWeiSum()),
						Constance.Null2EmptyTrim(bean.getUseStatus()),
						Constance.Null2EmptyTrim(bean.getFreezeFlag()),
						Constance.Null2EmptyTrim(bean.getRemark()),
						Constance.Null2EmptyTrim(bean.getOldTransKey()),
						Constance.Null2EmptyTrim(bean.getOldTransCardNo()),
						Constance.Null2EmptyTrim(bean.getRecUpdTs()),
						Constance.Null2EmptyTrim(bean.getOperator())
					};
				row = ExcelUtil.createRow(sheet, counts+3+i, col_i, data_style,
						true);
				List<FreezeListBean> freeze=freezeService.findByKey(String.valueOf(bean.getKeyStr()));				
				if(freeze.size()>0){
				for(int j=0;j<freeze.size();j++){
					FreezeListBean lb=freeze.get(j);
					String[] col_i2 = { 
							Constance.Null2EmptyTrim((i+1)+"-"+(j+1)),
							Constance.Null2EmptyTrim(lb.getTermId()),
							Constance.Null2EmptyTrim(lb.getCardAccptrNm()),
							Constance.Null2EmptyTrim(lb.getOverFreezeAmt()),
							Constance.Null2EmptyTrim(lb.getHaiNeedFreezeAmt()),
							Constance.Null2EmptyTrim(lb.getNowFreezeAmt()),
							Constance.Null2EmptyTrim(lb.getTransDt()),
							Constance.Null2EmptyTrim(lb.getTransTm()),
							Constance.Null2EmptyTrim(lb.getMaSettleDt()),
							Constance.Null2EmptyTrim(lb.getTranDtTm()),
							Constance.Null2EmptyTrim(lb.getPyeBankNo()),
							Constance.Null2EmptyTrim(lb.getPyeBankNm()),
							Constance.Null2EmptyTrim(lb.getPyeAcctNo()),
							Constance.Null2EmptyTrim(lb.getPyeAcctNm()),
							Constance.Null2EmptyTrim(lb.getPriBankNo()),
							Constance.Null2EmptyTrim(lb.getPriAcctNo()),
							Constance.Null2EmptyTrim(lb.getTranAmt()),
							Constance.Null2EmptyTrim(lb.getMchtStlmFee()),
							Constance.Null2EmptyTrim(lb.getT0StlmFee()),
							Constance.Null2EmptyTrim(lb.getStlmAmt())
						};

					row = ExcelUtil.createRow(sheet, counts+4+i+j, col_i2,general_style,
							true);
					}
					counts=counts+freeze.size();
				}		
			}
		} catch (Exception e) {
			logger.error("########导出强扣登记Excel明细失败######", e);
			throw new ServiceException("导出强扣登记Excel明细失败！");
		}
		return wb;
	}

	@Override
	public SXSSFWorkbook exportSettlementMsgFlow(
			List<TblTransJnlsSettle> settleList, String minDate, String maxDate,List<String> amtList) throws ServiceException {
		// TODO Auto-generated method stub
				SXSSFWorkbook  wb = new SXSSFWorkbook ();
				try {
					Map<String, String> respCodes = ConfigProperties
							.getRespCodeQueryTypes();
					SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("结算信息");
					CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
							false); // 首行标题样式对象
					CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
							true); // 数据标题样式对象
					CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
							false); // 普通样式对象
					Row row = sheet.createRow(0);
					Font font = wb.createFont();
					Cell cell = row.createCell((short) 0);
					CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
							(short) 14);
					sheet.addMergedRegion(region);
					cell.setCellValue("结算信息"); // 表格的第一行第一列显示的数据
					cell.setCellStyle(top_style); // 样式，居中
					String dateStr = minDate+"-"+maxDate;
					String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
							"日期范围", minDate, maxDate, "", "", "", "", "", "","","",""};
					row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
							true);
					
					String[] coloumValue2 = {"交易金额总额",amtList.get(0),"应结算金额总额",amtList.get(1),"手续费总额",amtList.get(2),"","","","","","","","",""};
					row = ExcelUtil.createRow(sheet, 2, coloumValue2, general_style, true);

					String[] coloumValue3 = { "商户号","商户名称","开户账号","开户名称","开户行行号","开户行名称","交易本金","结算金额",
							"商户手续费","结算类型","结算日期","结算周期","结算方式","机构代码","收单机构代码"};
					row = ExcelUtil.createRow(sheet, 3, coloumValue3, data_style, true);
					
					for (int i = 0; i < settleList.size(); i++) {
						TblTransJnlsSettle objs = settleList.get(i);

						String[] col_i = {objs.getMchtCd(),objs.getMchtName(),objs.getAccount(),objs.getAccountName(),
								objs.getBankCode(),objs.getBankName(),String.valueOf(objs.getTransAmt()),String.valueOf(objs.getMchtSetAmt()),
								String.valueOf(objs.getMchtFee()),objs.getTypeName(),objs.getSettleDate(),"日结","轧差",objs.getInsIdCd(),objs.getInsCompanyCd()};

						row = ExcelUtil.createRow(sheet, 4 + i, col_i, general_style,
								true);
						
					}
					
				} catch (Exception e) {
					logger.error("########导出交易Excel明细失败######", e);
					throw new ServiceException("导出交易Excel明细失败！");
				}
				return wb;
	}
	

	@Override
	public SXSSFWorkbook exportUnpayMcht(List<UnPayStatement> unlist) throws Exception {
		
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("银联直连商户交易报表");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,(short) 13);
			sheet.addMergedRegion(region);
			cell.setCellValue("银联直连商户交易报表"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"", "", "", "", "", "", "","","","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);
			String[] coloumValueTitle = { "商户号", "终端号", "总交易金额（元）","总手续费（元）","总结算金额（元）","交易日期","T1手续费率%",
					"T1封顶","T0手续费率%", "结算帐号","结算帐户名称", "结算银行","联行号", "银行所属ID"};
			row = ExcelUtil.createRow(sheet, 2, coloumValueTitle, data_style, true);
			for (int i = 0; i < unlist.size(); i++) {
				UnPayStatement up =  unlist.get(i);
				String[] col_i = { Constance.Null2EmptyTrim(up.getMerchantNo()),
						Constance.Null2EmptyTrim(up.getTerminalNo()),
						Constance.Null2EmptyTrim(up.getSumAmt()),
						Constance.Null2EmptyTrim(up.getSumFee()),
						Constance.Null2EmptyTrim(up.getSumSettleamt()),
						Constance.Null2EmptyTrim(up.getTransDate()),
						Constance.Null2EmptyTrim(up.getFeeT1()),
						Constance.Null2EmptyTrim(up.getFeeT1Top()),
						Constance.Null2EmptyTrim(up.getFeeT0()),
						Constance.Null2EmptyTrim(up.getAccountNo()),
						Constance.Null2EmptyTrim(up.getAccountName()),
						Constance.Null2EmptyTrim(up.getBankName()),
						Constance.Null2EmptyTrim(up.getBankCode()),
						Constance.Null2EmptyTrim(up.getYlId())
					};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);				
			}
		} catch (Exception e) {
			logger.error("########导出银联直连商户交易报表失败######", e);
			throw new ServiceException("导出银联直连商户交易报表失败！");
		}
		return wb;
	}

	@Override
	public SXSSFWorkbook exportUnpay(List<UnPay> list) throws Exception {
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("银联直连商户信息");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,(short) 12);
			sheet.addMergedRegion(region);
			cell.setCellValue("银联直连商户信息"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"", "", "", "", "", "", "","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);
			String[] coloumValueTitle = { "商户号", "终端号", "T1手续费%", "T1手续费封顶(元)", "T0手续费%",
					"结算帐号", "结算帐户名称","结算银行","联行号","直连所属银行ID","添加日期","操作员","备注"};
			row = ExcelUtil.createRow(sheet, 2, coloumValueTitle, data_style, true);
			for (int i = 0; i < list.size(); i++) {
				UnPay un =  list.get(i);
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				
				DecimalFormat df = new DecimalFormat("#.##");
				
				String feeT1 = Constance.Null2EmptyTrim(un.getFeeT1());
				String feeT1Top =Constance.Null2EmptyTrim(un.getFeeT1Top());
				String feeT0 = Constance.Null2EmptyTrim(un.getFeeT0());
				feeT1 = df.format(new BigDecimal(feeT1).multiply(new BigDecimal(100))); 
				feeT1Top = df.format(new BigDecimal(feeT1Top).divide(new BigDecimal(100))); 
				feeT0 = df.format(new BigDecimal(feeT0).multiply(new BigDecimal(100))); 
				
				String[] col_i = { Constance.Null2EmptyTrim(un.getMerchantNo()),
						Constance.Null2EmptyTrim(un.getTerminalNo()),
						feeT1,
						feeT1Top,
						feeT0,
						Constance.Null2EmptyTrim(un.getAccountNo()),
						Constance.Null2EmptyTrim(un.getAccountName()),
						Constance.Null2EmptyTrim(un.getBankName()),
						Constance.Null2EmptyTrim(un.getBankCode()),
						Constance.Null2EmptyTrim(un.getYlId()),
						sdf.format(un.getAddTime()),
						Constance.Null2EmptyTrim(un.getOper()),
						Constance.Null2EmptyTrim(un.getRemark())
					};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,true);				
			}
		} catch (Exception e) {
			logger.error("########导出银联直连商户信息失败######", e);
			throw new ServiceException("导出银联直连商户信息失败！");
		}
		return wb;
	}

	public SXSSFWorkbook exportApplyMoneyList(
			List<TblSettleItem> settleItemList) throws Exception {
			String title="出款信息";
			SXSSFWorkbook  wb = new SXSSFWorkbook ();
			try {
				SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(title);
				CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
						false); // 首行标题样式对象
				CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
						true); // 数据标题样式对象
				CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
						false); // 普通样式对象
				Row row = sheet.createRow(0);
				Font font = wb.createFont();
				Cell cell = row.createCell((short) 0);
				CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
						(short) 16);
				sheet.addMergedRegion(region);
				cell.setCellValue(title); // 表格的第一行第一列显示的数据
				cell.setCellStyle(top_style); // 样式，居中
				String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
						"", "", "", "", "", "", "", "","","","","","","",""};
				row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
						true);
				String[] coloumValue2={"结算ID","商户号","商户名 称","出款状态","出款金额（元）",
						"商户手续费（元）","冻结金额（元）","结算金额（元）","交易金额（元）","账号性 质","出款类型","账号名称","结算银行账号","联行号","结算银行","原始流水号","新上送流水号","结算日期","交易日期"};
					
				row = ExcelUtil.createRow(sheet, 2, coloumValue2, data_style, true);
				
				for (int i = 0; i < settleItemList.size(); i++) {
					TblSettleItem objs = settleItemList.get(i);
					String quality="",type="";
					if("01".equals(Constance.Null2EmptyTrim(objs.getAccType()))){
						quality="对公";
					}else if("02".equals(Constance.Null2EmptyTrim(objs.getAccType()))){
						quality="对私";
					}else{
						quality="未知";
					}
					
					if("0".equals(Constance.Null2EmptyTrim(objs.getRemark()))){
						type="普通交易出款";
					}else if("1".equals(Constance.Null2EmptyTrim(objs.getRemark()))){
						type="扫码出款";
					}else if("2".equals(Constance.Null2EmptyTrim(objs.getRemark()))){
						type="强扣放款";
					}else{
						type="未知";
					}
					String[] col_i = {
						String.valueOf(objs.getId()),				
						objs.getMchtCd(),
						objs.getMchtName(),
						objs.getSettleFlag(),
						String.valueOf(objs.getUnFreezeAmt()),
						String.valueOf(objs.getMchtFee()),
						String.valueOf(objs.getFreezeAmt()),
						String.valueOf(objs.getMchtSetAmt()),
						String.valueOf(objs.getTransAmt()),
						quality,
						type,
						objs.getAccountName(),					
						objs.getAccount(),
						objs.getBankCode(),
						objs.getBankName(),
						objs.getRemark1(),
						objs.getSerialNo(),
						objs.getSettleDate(),
						objs.getSetDate()
						};
					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
				}			
			} catch (Exception e) {
				logger.error("########导出"+title+"Excel明细失败######", e);
				throw new ServiceException("导出"+title+"Excel明细失败！");
			}
			return wb;
	}
	@Override
	public SXSSFWorkbook exportBlackList(List<BlackListBean> blackList,
			String minDate, String maxDate)  throws Exception {

		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("黑名单");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 17, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 5);
			sheet.addMergedRegion(region);
			cell.setCellValue("黑名单"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate()};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);
			String[] coloumValue3 = { "号码", "类型", "日期", "时间", "原因", "状态" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			for (int i = 0; i < blackList.size(); i++) {
				BlackListBean objs = blackList.get(i);
				   String blackType ="";
                 if(objs.getBlackType().equals("C")){
                	 blackType="黑卡";
                 }else if(objs.getBlackType().equals("M")){
                	 blackType="黑商户";
                 }else if(objs.getBlackType().equals("D")){
                	 blackType="法人身份证";
                 }else if(objs.getBlackType().equals("N")){
                	 blackType="非法人身份证";
                 }else if(objs.getBlackType().equals("P")){
                	 blackType="手机号";
	             }else if(objs.getBlackType().equals("O")){
	            	 blackType="营业执照";
                 }
                 String sta ="";
                 if(objs.getStatus().equals("1")){
                	 sta="冻结";
                 }else if(objs.getStatus().equals("0")){
                	 sta="解冻";
                 }else {
                	 sta="待审核";
                 }                	 
				String[] col_i = { 
						Constance.Null2EmptyTrim(objs.getBlackNum()),
						Constance.Null2EmptyTrim(blackType),
						//Constance.Null2EmptyTrim(objs.getBlackType()),
						Constance.Null2EmptyTrim(objs.getDate()),
						Constance.Null2EmptyTrim(objs.getTime()),
						Constance.Null2EmptyTrim(objs.getReason()),
						Constance.Null2EmptyTrim(sta),
				};
				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,true);
			}

		} catch (Exception e) {
			logger.error("########导出黑名单Excel失败######", e);
			throw new ServiceException("导出黑名单Excel失败！");
		}
		return wb;
	}

	@Override
	public SXSSFWorkbook exportTermActivateStaList( List<TblTerActivateStatistics> termList, Map<String, Map<String, String>> snStaInfoMap, 
				String startingDate, String endDate, String effectAct,Operators logingMerchant) throws Exception {
		SXSSFWorkbook wb = new SXSSFWorkbook();
		String name = "终端sn号段";
		Map<String, Map<String, String>> snStaMap = new HashMap<String, Map<String, String>>();
			try {
				if(Constant.STR_NUMBER_THREE.equals(effectAct)){
					 name = "有效商户";
				}
				SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(name + "激活详情");
				
				setTerminalStaColWidth(sheet); // 设置每列宽度
				CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
						false); // 首行标题样式对象
				CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
						true); // 数据标题样式对象
				CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
						false); // 普通样式对象
				Row row = sheet.createRow(0);
				Font font = wb.createFont();
				Cell cell = row.createCell((short) 0);
				CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
						(short) 8);
				sheet.addMergedRegion(region);
				cell.setCellValue(Constance.formateDate()); // 表格的第一行第一列显示的数据
				cell.setCellStyle(top_style); // 样式，居中
			
				String[] coloumValue3 = { "所属分公司","一级代理商名称","代理商名称", "SN","厂商","商户号","终端", "绑定日期","激活日期"};
				row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, true);
				
				for (int i = 0; i < termList.size(); i++) {
					TblTerActivateStatistics objs = termList.get(i);

					String agentName = Constance.Null2EmptyTrim( objs.getAgentName() );
					String companyName = Constance.Null2EmptyTrim( objs.getCompanyName() ) ;
					String oneAgentName = Constance.Null2EmptyTrim( objs.getOneagentName() );
					
					
					String[] col_i = { companyName,
							oneAgentName,
							agentName,
							Constance.Null2EmptyTrim(objs.getSerialNum()),
							Constance.Null2EmptyTrim(objs.getPosBrand()),
							Constance.Null2EmptyTrim(objs.getMchntCd()),
							Constance.Null2EmptyTrim(objs.getTermId()),
							Constance.Null2EmptyTrim(objs.getBrandDate()),
							Constance.Null2EmptyTrim(objs.getTrnDate())};

					row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
							true);

					String oneAgentId = String.valueOf( objs.getOneagentId() );
					if ( 0 >= snStaMap.size() ||  !snStaMap.containsKey(oneAgentId) ){

						Map<String, String> contextMap = new HashMap<String, String>();
						contextMap.put("count", "1" );
						contextMap.put("companyName", companyName);
						contextMap.put("oneAgentName", oneAgentName);
						
						snStaMap.put( oneAgentId ,  (contextMap) );
					}else {
						Map<String, String> contextMap = snStaMap.get(oneAgentId);
						Integer count = (Integer.parseInt(contextMap.get("count")) + 1);
						contextMap.put("count", String.valueOf(count));
						
						snStaMap.put( oneAgentId ,  (contextMap) );
						
					}
		/*			String agentId = String.valueOf( Constance.Null2EmptyTrim(objs.getAgentId()) );
					
					if( snStaMap.containsKey(agentId) ) {

						Map<String, String> contextMap = snStaMap.get(agentId);
						Integer count = (Integer.parseInt(contextMap.get("count")) + 1);
						contextMap.put("count", String.valueOf(count));
						snStaMap.put( agentId ,  (contextMap) );
					}*/
				}
				

				// 登录账号为 管理员 或者 收单事业部,才有权限 导出统计信息
				if(logingMerchant.getRoleType().equals(Role.SYSTEM.getCode()) 
						|| Constant.SHOUDANSHIYEBU_ID.equals( String.valueOf(logingMerchant.getId()) ) ) {
	
					//数据统计
					SXSSFSheet sheet_data = (SXSSFSheet) wb.createSheet(name + "激活统计数据");
					Row row_data = sheet_data.createRow(0);
					Cell cell_data = row_data.createCell((short) 0);
					CellRangeAddress region_data = new CellRangeAddress((short) 0, (short) 0, (short) 0,
							(short) 2);
					sheet_data.addMergedRegion(region_data);
					cell_data.setCellValue(name + "激活统计数据"); // 表格的第一行第一列显示的数据
					cell_data.setCellStyle(top_style); // 样式，居中
					String[] coloumValueTitle = { "所属分公司", "一级代理商名称" , "激活数量" };
					row = ExcelUtil.createRow(sheet_data, 1, coloumValueTitle, data_style,
							true);			
	
					int k = 0;
					Integer activateCounts = 0;// 激活数量
			//		Integer unActivateCounts = 0;// 激活数量
			//		Integer allActivateNums = 0;// 总数量
					for (String key : snStaMap.keySet()) {
						Map<String, String> contextMap = snStaMap.get(key);
	
			//			Integer allNum = Integer.parseInt( contextMap.get("allNum") );
						Integer activateNum = Integer.parseInt( contextMap.get("count") );
			//			Integer unActivateCount = allNum - activateNum ;
						
						String[] col_i = { contextMap.get("companyName") ,
								contextMap.get("oneAgentName") , 
						//		contextMap.get("agentName") , 
								String.valueOf( activateNum ) //, 
							//	String.valueOf( unActivateCount ) , 
							//	String.valueOf( allNum ) 
								};
						row_data = ExcelUtil.createRow(sheet_data, 2 + k, col_i, general_style, true);
						activateCounts = activateCounts + activateNum ;
				//		unActivateCounts = unActivateCounts + unActivateCount ;
				//		allActivateNums = allActivateNums + allNum ;
						
						k++;
					}
					DecimalFormat df = new DecimalFormat("0");
					String[] coloumValue_data = { "总计：", "", df.format(activateCounts)};
					row_data = ExcelUtil.createRow(sheet_data, snStaMap.size() + 2, coloumValue_data,
							general_style, true);
				}
			} catch (Exception e) {
				logger.error("########导出" + name + "激活统计Excel明细失败######", e);
				throw new ServiceException("导出" + name + "激活统计Excel明细失败！");
			}
			return wb;
	}

	@Override
	public SXSSFWorkbook exportTermSelfSupportActivateStaList( List<TblTerActivateStatistics> termList, Map<String, Map<String, String>> snStaInfoMap, 
				String effectAct,Operators logingMerchant) throws Exception {
		SXSSFWorkbook wb = new SXSSFWorkbook();
		String name = Constant.STR_NUMBER_TWO.equals(effectAct) ? "自营终端有效商户" : "自营终端";
		Map<String, Map<String, String>> snStaMap = new HashMap<String, Map<String, String>>();
		try {
				SXSSFSheet sheet = (SXSSFSheet) wb.createSheet( name + "激活统计详情");
				setTerminalStaColWidth(sheet); // 设置每列宽度
				CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
						false); // 首行标题样式对象
				CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
						true); // 数据标题样式对象
				CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
						false); // 普通样式对象
				Row row = sheet.createRow(0);
				Font font = wb.createFont();
				Cell cell = row.createCell((short) 0);
				CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
						(short) 8);
				sheet.addMergedRegion(region);
				cell.setCellValue(Constance.formateDate()); // 表格的第一行第一列显示的数据
				cell.setCellStyle(top_style); // 样式，居中
			
				String[] coloumValue3 = { "所属分公司","一级代理商名称","代理商名称", "SN","厂商","商户号","终端", "绑定日期","激活日期"};
				row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, true);
				
				for (int i = 0; i < termList.size(); i++) {
					TblTerActivateStatistics objs = termList.get(i);

					String agentName = Constance.Null2EmptyTrim( objs.getAgentName() );
					String companyName = Constance.Null2EmptyTrim( objs.getCompanyName() ) ;
					String oneAgentName = Constance.Null2EmptyTrim( objs.getOneagentName() );
					
					
					String[] col_i = { companyName,
							oneAgentName,
							agentName,
							Constance.Null2EmptyTrim(objs.getSerialNum()),
							Constance.Null2EmptyTrim(objs.getPosBrand()),
							Constance.Null2EmptyTrim(objs.getMchntCd()),
							Constance.Null2EmptyTrim(objs.getTermId()),
							Constance.Null2EmptyTrim(objs.getBrandDate()),
							Constance.Null2EmptyTrim(objs.getTrnDate())};

					row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
							true);
					String oneAgentId = String.valueOf( objs.getOneagentId() );
					
					if ( 0 >= snStaMap.size() ||  !snStaMap.containsKey(oneAgentId) ){

						Map<String, String> contextMap = new HashMap<String, String>();
						contextMap.put("count", "1" );
						contextMap.put("companyName", companyName);
						contextMap.put("oneAgentName", oneAgentName);
						
						snStaMap.put( oneAgentId ,  (contextMap) );
					}else {
						Map<String, String> contextMap = snStaMap.get(oneAgentId);
						Integer count = (Integer.parseInt(contextMap.get("count")) + 1);
						contextMap.put("count", String.valueOf(count));
						
						snStaMap.put( oneAgentId ,  (contextMap) );
						
					}
					
			/*		String agentId = String.valueOf( Constance.Null2EmptyTrim(objs.getAgentId()) );
					
					if( snStaMap.containsKey(agentId) ) {

						Map<String, String> contextMap = snStaMap.get(agentId);
						Integer count = (Integer.parseInt(contextMap.get("count")) + 1);
						contextMap.put("count", String.valueOf(count));
						snStaMap.put( agentId ,  (contextMap) );
					}*/
				}
				// 登录账号为 管理员 或者 收单事业部,才有权限 导出统计信息
				if(logingMerchant.getRoleType().equals(Role.SYSTEM.getCode()) 
						|| Constant.SHOUDANSHIYEBU_ID.equals( String.valueOf(logingMerchant.getId()) ) ) {

					//数据统计
					SXSSFSheet sheet_data = (SXSSFSheet) wb.createSheet(name + "激活统计数据");
					Row row_data = sheet_data.createRow(0);
					Cell cell_data = row_data.createCell((short) 0);
					CellRangeAddress region_data = new CellRangeAddress((short) 0, (short) 0, (short) 0,
							(short) 2);
					sheet_data.addMergedRegion(region_data);
					cell_data.setCellValue("自营终端激活统计数据"); // 表格的第一行第一列显示的数据
					cell_data.setCellStyle(top_style); // 样式，居中
					String[] coloumValueTitle = { "所属分公司", "一级代理商名称" , "激活数量"};
					row = ExcelUtil.createRow(sheet_data, 1, coloumValueTitle, data_style,
							true);			
	
					int k = 0;
					Integer activateCounts = 0;// 激活数量
					Integer unActivateCounts = 0;// 激活数量
					Integer allActivateNums = 0;// 总数量
					for (String key : snStaMap.keySet()) {
						Map<String, String> contextMap = snStaMap.get(key);
	
				//		Integer allNum = Integer.parseInt( contextMap.get("allNum") );
						Integer activateNum = Integer.parseInt( contextMap.get("count") );
				//		Integer unActivateCount = allNum - activateNum ;
						
						String[] col_i = { contextMap.get("companyName") ,
								contextMap.get("oneAgentName") , 
							//	contextMap.get("agentName") , 
								String.valueOf( activateNum ) //, 
							//	String.valueOf( unActivateCount ) , 
							//	String.valueOf( allNum ) 
								};
						row_data = ExcelUtil.createRow(sheet_data, 2 + k, col_i, general_style, true);
						activateCounts = activateCounts + activateNum ;
					//	unActivateCounts = unActivateCounts + unActivateCount ;
					//	allActivateNums = allActivateNums + allNum ;
						
						k++;
					}
					DecimalFormat df = new DecimalFormat("0");
					String[] coloumValue_data = { "总计：", "", df.format(activateCounts) };
					row_data = ExcelUtil.createRow(sheet_data, snStaMap.size() + 2, coloumValue_data,
							general_style, true);
				}
				
			} catch (Exception e) {
				logger.error("########导出" + name + "活统计Excel明细失败######", e);
				throw new ServiceException("导出" + name + "激活统计Excel明细失败！");
			}
			return wb;
	}

	@Override
	public HSSFWorkbook exportChangeCheckList(List<ChangeCheckCountBean> ccList, String minDate, String endDate)throws Exception {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			HSSFSheet sheet = wb.createSheet("变更审核统计");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 5);
			sheet.addMergedRegion(region);
			cell.setCellValue("变更审核统计"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+endDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"变更审核统计日期范围", minDate, endDate};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "序号", "操作员账号", "操作员姓名", "领件总数", "审核通过数量",
					"审核不通过数量"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			
			for (int i = 0; i < ccList.size(); i++) {
				ChangeCheckCountBean objs = ccList.get(i);
				
				String[] col_i = { String.valueOf((i+1)),
						Constance.Null2EmptyTrim(objs.getAccountNo()),
						Constance.Null2EmptyTrim(objs.getRealName()),
						Constance.Null2EmptyTrim(objs.getTotal()),
						Constance.Null2EmptyTrim(objs.getPassCount()),
						Constance.Null2EmptyTrim(objs.getNoPassCount())};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
			}

		} catch (Exception e) {
			logger.error("########导出变更审核统计信息异常######", e);
			throw new ServiceException("导出变更审核统计信息失败！");
		}
		return wb;
	
	}

	@Override
	public HSSFWorkbook exportChangeCheckAgentList(List<ChangeAgentCountBean> ccList, String statDate, String endDate) throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			HSSFSheet sheet = wb.createSheet("变更审核统计");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 5);
			sheet.addMergedRegion(region);
			cell.setCellValue("变更审核统计"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = statDate+"-"+endDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(),
					"变更审核统计日期范围", statDate, endDate,""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "代理商ID", "代理商名称","所属分公司", "变更类型",  "审核通过数量",
					"审核不通过数量"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			
			for (int i = 0; i < ccList.size(); i++) {
				ChangeAgentCountBean objs = ccList.get(i);
				String dealName="";
				if("C1".equals(objs.getDealCode())){dealName="变更基本信息";}
				else if("C2".equals(objs.getDealCode())){dealName="变更结算信息";}
				else if("C3".equals(objs.getDealCode())){dealName="增机申请";}
				else if("C4".equals(objs.getDealCode())){dealName="变更T1手续费";}
				else if("C5".equals(objs.getDealCode())){dealName="变更T0手续费";}
				else if("C6".equals(objs.getDealCode())){dealName="申请开通T0";}
				else if("C7".equals(objs.getDealCode())){dealName="申请开通T1";}
				else if("C8".equals(objs.getDealCode())){dealName="终止合同";}
				else if("C9".equals(objs.getDealCode())){dealName="变更商户等级";}
				else if("10".equals(objs.getDealCode())){dealName="商户申请银联扫码支付开通";}
				else if("11".equals(objs.getDealCode())){dealName="商户申请支付宝/微信扫码支付开通";}
				
				String[] col_i = { 
						Constance.Null2EmptyTrim(objs.getAgentId()),
						Constance.Null2EmptyTrim(objs.getAgentName()),
						Constance.Null2EmptyTrim(objs.getCompanyName()),
						Constance.Null2EmptyTrim(dealName),
						//Constance.Null2EmptyTrim(objs.getTotal()),
						Constance.Null2EmptyTrim(objs.getPassCount()),
						Constance.Null2EmptyTrim(objs.getNoPassCount())};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
			}

		} catch (Exception e) {
			logger.error("########导出变更审核统计信息异常######", e);
			throw new ServiceException("导出变更审核统计信息失败！");
		}
		return wb;
	
	}
	
	
	
	@Override
	public SXSSFWorkbook exportAccountStaList(List<AccountingStatistics> asList,
			String transDt) throws ServiceException{

		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("占比统计");
			setTerminalStaColWidth(sheet); // 设置每列宽度
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 7);
			sheet.addMergedRegion(region);
			cell.setCellValue("占比统计"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			/*String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""
					, "", "", "", "", "", "", "", ""};*/
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(),  "", "", "", "", "", "", "", "", "", "", "", "", "", ""
					, "", "", "", "", "", "", "", ""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			/*String[] coloumValue3 = { "日期", "所属分公司", "直属代理商", "32域对应省份", "32域对应城市",
					"商户号","审核通过日期","商户状态","信用卡交易占比","刷磁交易占比","总交易笔数","高风险返回码交易笔数","高风险返回码交易占比","总成功笔数","总成功交易金额",
					"总成功交易占比","总失败笔数","总失败交易占比","夜间交易成功笔数","夜间成功交易金额","夜间交易失败笔数","夜间交易笔数","夜间交易失败占比","余额查询失败笔数",
					"余额查询总笔数","余额查询交易失败占比"};*/
			
			String[] coloumValue3 = { "日期", "32域对应省份", "32域对应城市",
					"商户号","审核通过日期","商户状态","信用卡交易占比","刷磁交易占比","总交易笔数","高风险返回码交易笔数","高风险返回码交易占比","总成功笔数","总成功交易金额",
					"总成功交易占比","总失败笔数","总失败交易占比","夜间交易成功笔数","夜间成功交易金额","夜间交易失败笔数","夜间交易笔数","夜间交易失败占比","余额查询失败笔数",
					"余额查询总笔数","余额查询交易失败占比"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < asList.size(); i++) {
				AccountingStatistics objs = asList.get(i);

				//System.out.println("金额：" + objs.getAmountConver());

				
				String[] col_i = {Constance.Null2EmptyTrim(objs.getTransDt()),
						/*Constance.Null2EmptyTrim(objs.getRoutIndustryInsIdCd()),
						Constance.Null2EmptyTrim(objs.getExt1Name()),*/
						Constance.Null2EmptyTrim(objs.getProvCd()),
						Constance.Null2EmptyTrim(objs.getCityCd()),
						Constance.Null2EmptyTrim(objs.getMchtCd()),
						Constance.Null2EmptyTrim(objs.getApplDate()),
						Constance.Null2EmptyTrim(objs.getStatus()),
						Constance.Null2EmptyTrim(objs.getCreditAccounted()),
						Constance.Null2EmptyTrim(objs.getBrushAccounted()),
						Constance.Null2EmptyTrim(objs.getBusinessCount()),
						Constance.Null2EmptyTrim(objs.getHighReturnCount()),
						Constance.Null2EmptyTrim(objs.getHighRiskReturnAccount()),
						Constance.Null2EmptyTrim(objs.getTotalCount()),
						Constance.Null2EmptyTrim(objs.getTotalAmount()),
						Constance.Null2EmptyTrim(objs.getTotalSuccessAccount()),
						Constance.Null2EmptyTrim(objs.getTotalSuccessCount()),
						Constance.Null2EmptyTrim(objs.getTotalFailedAccount()),
						Constance.Null2EmptyTrim(objs.getNightSuccessCount()),
						Constance.Null2EmptyTrim(objs.getNightSuccessAmt()),
						Constance.Null2EmptyTrim(objs.getNightFailedCount()),
						Constance.Null2EmptyTrim(objs.getNightCount()),
						Constance.Null2EmptyTrim(objs.getNightFailedAccount()),
						Constance.Null2EmptyTrim(objs.getBalanceQueryFailedCount()),
						Constance.Null2EmptyTrim(objs.getBalanceQueryCount()),
						Constance.Null2EmptyTrim(objs.getBalanceQueryFailedAccount())};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				// amt=amt+Double.parseDouble(
				// Constance.isMoney(objs[2].toString().trim()));
			}

		} catch (Exception e) {
			logger.error("########导出风控占比统计信息异常######", e);
			throw new ServiceException("导出风控占比统计信息失败！");
		}
		return wb;
	
	
	}

	@Override
	public HSSFWorkbook exportAgentSubsidyList(List<AgentSubsidyBean> agentList) throws Exception {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			HSSFSheet sheet = wb.createSheet("代理商补贴详情");	
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			 general_style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中    
		     general_style.setWrapText(true);//设置自动换行
			HSSFRow row = sheet.createRow(0);
			
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 16);
			
			sheet.addMergedRegion(region);
			cell.setCellValue("代理商补贴详情"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue3 = { "  商户号    ", "商户名称", "法人代表", "法人身份证","营业执照号码","直属代理商名称",
					"一级代理商名称","所属大区", "商户结算账户", "商户结算账户名称","商户结算银行","终端序列号","总金额","总笔数","交易月份","商户状态","终端状态"};
			row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, false);
			for (int i = 0; i < agentList.size(); i++) {
				AgentSubsidyBean objs = agentList.get(i);
				String MerStatus="";
				if("01".equals(objs.getMerinfoStatus())){MerStatus="正常";}
				else if("02".equals(objs.getMerinfoStatus())){MerStatus="注销";}
				else if("03".equals(objs.getMerinfoStatus())){MerStatus="冻结";}
				String termStatus="";
				if("01".equals(objs.getMerinfoStatus())){termStatus="正常";}
				else if("02".equals(objs.getMerinfoStatus())){termStatus="已解绑";}
				else if("03".equals(objs.getMerinfoStatus())){termStatus="冻结";}
				
				String[] col_i = { Constance.Null2EmptyTrim(objs.getMchtCd()),
						Constance.Null2EmptyTrim(objs.getNameBusi()),
						Constance.Null2EmptyTrim(objs.getCertif()),
						Constance.Null2EmptyTrim(objs.getCertifNo()),
						Constance.Null2EmptyTrim(objs.getBusiLiceNo()),
						Constance.Null2EmptyTrim(objs.getAgentName()),
						Constance.Null2EmptyTrim(objs.getOneAgentName()),
						Constance.Null2EmptyTrim(objs.getCompanyName()),
						Constance.Null2EmptyTrim(objs.getMerchantBankAccount()),
						Constance.Null2EmptyTrim(objs.getMerchantBankAccountName()),
						Constance.Null2EmptyTrim(objs.getMerchantBankName()),
						Constance.Null2EmptyTrim(objs.getDeviceCd()),
						Constance.Null2EmptyTrim(objs.getAllAmt()),
						Constance.Null2EmptyTrim(objs.getAllCount()),
						Constance.Null2EmptyTrim(objs.getTransMonth()),
						Constance.Null2EmptyTrim(MerStatus),
						Constance.Null2EmptyTrim(termStatus),
				};
			
				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						true);
			}

		} catch (Exception e) {
			logger.error("########导出代理商补贴详情失败######", e);
			throw new ServiceException("导出代理商补贴详情失败！");
		}
		return wb;
 }
	//以前的导出差错
	@Override
	public SXSSFWorkbook exportOrderProcessList(List<Object> searchRsList)
			throws ServiceException {
		
		SXSSFWorkbook  wb = new SXSSFWorkbook ();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("差错列表");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 13);
			sheet.addMergedRegion(region);
			cell.setCellValue("差错列表"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表导出日期：", Constance.formateDate(), "",
					"", "", "", "", "", "", "", "", "","","","","","","","","","","","","","","",""
					,"","","","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = {"商户号","交易账号","系统参考号","交易金额","清算/交易 日期","	查询","查询日期","二次查询",
					"二次查询日期","调单","调单日期","二次调单","二次调单日期","贷记调整","贷记调整日期","请款","请款日期","退单",
					"退单日期","二次请款","二次请款日期","二次退单","二次退单日期","例外协商","例外协商日期","例外长款","	例外长款日期",
					"托收请款","托收请款日期","托收协商","托收协商日期","	操作员"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < searchRsList.size(); i++) {
				RiskSurveyOrder objs = (RiskSurveyOrder) searchRsList.get(i);

				String[] col_i = {objs.getMerchantNo(),objs.getAccountNo(),objs.getSystemNo(),objs.getTransAmt(),
						objs.getTransDate(),objs.getFirstQuery(),objs.getFirstQueryDate(),objs.getSecondQuery()
						,objs.getSecondQueryDate(),objs.getFirstSurvey(),objs.getFirstSurveyDate(),objs.getSecondSurvey(),
						objs.getSecondSurveyDate(),objs.getLoanAdjust(),objs.getLoanAdjustDate(),objs.getFirstWithdrawCash(),
						objs.getFirstWithDrawCashDate(),objs.getFirstChargeBack(),objs.getFirstChargeBackDate(),
						objs.getSecondWithDrawCash(),objs.getSecondWithDrawCashDate(),objs.getSecondChargeBack(),
						objs.getSecondChargeBackDate(),objs.getExceptionNegotiate(),objs.getExceptionNegotiateDate(),
						objs.getExceptionLongMoney(),objs.getExceptionLongMoneyDate(),objs.getCollectionWithDrawCash(),
						objs.getCollectionWithDrawCashDate(),objs.getCollectionNegotiate(),objs.getCollectionNegotiateDate(),
						objs.getOper()};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				
			}

		} catch (Exception e) {
			logger.error("########导出差错明细失败######", e);
			throw new ServiceException("导出差错明细失败！");
		}
		return wb;

	
	}
 

		
	//导出差错
	@Override
	public SXSSFWorkbook exErrorCountList(List<ErrorCountBean> searchRsList,
			String minDate, String maxDate)
			throws ServiceException {
		
		
		SXSSFWorkbook  wb = new SXSSFWorkbook ();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("差错交易列表");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 13);
			sheet.addMergedRegion(region);
			cell.setCellValue("差错交易列表"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表导出日期：",  " ---",
					" ",  "", "", "", "", "", "", "","","","","","","","","","","","","","","",""
					,"","","","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = {"调单日期","调单类型","调单原因码","所属分公司","商户编号","	系统跟踪号","卡号","卡片性质",
					"卡片介质","一级代理商","直属代理商","商户名","交易日期","原交易传输时间","原始交易金额","应答码","系统登记地址","系统登记联系人",
					"系统登记联系方式","检索参考号","再请款日期","省","市","商户入网时间","终端号","D0/T1","是否四要素认证",
					"发卡行","	32域"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < searchRsList.size(); i++) {
				ErrorCountBean objs = (ErrorCountBean) searchRsList.get(i);

				String[] col_i = {
						objs.getFirstSurveyDate(),
						objs.getFirstSurvey(),
						objs.getReasonCode(),
						objs.getCompanyid(),
						objs.getMerchantNo(),
						objs.getTransSeqConv(),
						objs.getAccountNo(),
						objs.getCardClass(),
						objs.getPosEntryMdCd(),
						objs.getOnegentid(),
						objs.getsAgentName(),
						objs.getName(),
						objs.getTransDate(),
						objs.getTransMt(),
						objs.getTransAt(),
						objs.getRespCd(),
						objs.getRegAddr(),
						objs.getContactName(),
						objs.getContactPhoneno(),
						objs.getRetriRefNo(),
						objs.getSecondWithdrawCashDate(),
						objs.getProvCd(),
						objs.getCityCd(),
						objs.getRecCrtTs(),
						objs.getTermId(),
						objs.getMaTransCd(),
						objs.getFourElements(),
						objs.getInsIdNm(),
						objs.getUcBcCd32(),
						
						};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				
			}

		} catch (Exception e) {
			logger.error("########导出差错明细失败######", e);
			throw new ServiceException("导出差错交易统计失败！");
		}
		return wb;
	}

     // 现在的导出差错
	@Override
//	public SXSSFWorkbook exportErrorCountList(List<ErrorCountBean> sList,
//			String minDate, String maxDate) throws ServiceException {
		public SXSSFWorkbook exportErrorCountList(List<ErrorCountBean> sList , OrderProcessSearchCondition searchCondition)throws ServiceException{
	
		
		SXSSFWorkbook  wb = new SXSSFWorkbook ();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("差错交易列表");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 13);
			sheet.addMergedRegion(region);
			cell.setCellValue("差错交易列表"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表导出日期：", searchCondition.getMinDate(), " ---",
					searchCondition.getMaxDate(), "", "", "", "", "", "", "","","","","","","","","","","","","","","",""
					,"","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);
			
			String typeDate;
			String  type = Constant.REASON_MAP.get(searchCondition.getOrderType());
			if(type==null||type==""){
				typeDate ="";
			}else{typeDate=type;}
			
			String[] coloumValue3 = {typeDate+"日期","调单类型","调单原因码","所属分公司","商户编号","	系统跟踪号","卡号","卡片性质",
					"卡片介质","一级代理商","直属代理商","商户名","交易日期","原交易传输时间","原始交易金额","应答码","系统登记地址","系统登记联系人",
					"系统登记联系方式","检索参考号","再请款日期","省","市","商户入网时间","终端号","T0/T1","是否四要素认证",
					"发卡行","	32域"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			for (int i = 0; i < sList.size(); i++) {
				ErrorCountBean objs = (ErrorCountBean) sList.get(i);

				String[] col_i = {
						objs.getDate(),
						objs.getOrderType(),
						objs.getReasonCode(),
//						searchCondition.getReasonCd(),
						objs.getCompanyid(),
						objs.getMerchantNo(),
						objs.getTransSeqConv(),
						objs.getAccountNo(),
						objs.getCardClass(),
						objs.getPosEntryMdCdName(),
						objs.getOnegentid(),
						objs.getsAgentName(),
						objs.getName(),
						objs.getTransDate(),
						objs.getTransMt(),
						objs.getTransAt(),
						objs.getRespCd(),
						objs.getRegAddr(),
						objs.getContactName(),
						objs.getContactPhoneno(),
						objs.getRetriRefNo(),
						objs.getSecondWithdrawCashDate(),
						objs.getProvCd(),
						objs.getCityCd(),
						objs.getRecCrtTs(),
						objs.getTermId(),
						objs.getMaTransCd(),
						objs.getFourElements(),
						objs.getInsIdNm(),
						objs.getUcBcCd32(),
						
						};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
				
			}

		} catch (Exception e) {
			logger.error("########导出差错明细失败######", e);
			throw new ServiceException("导出差错交易统计失败！");
		}
		return wb;
	}

	
	 /**
     * 导出整改商户数据信息
     */
	@Override
	public HSSFWorkbook exportReviseDataExcel(Map<String,Object> map)throws ServiceException {
		// TODO Auto-generated method stub
		
			HSSFWorkbook wb = new HSSFWorkbook();
			try {
				Map<String, String> respCodes  =  new LinkedHashMap<String, String>();
				respCodes= ConfigProperties
						.getRespCodeQueryTypes();
				HSSFSheet sheet = wb.createSheet("整改商户数据信息");
				
				 HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
                         false); // 首行标题样式对象
				HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
						true); // 数据标题样式对象
				HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
						false); // 普通样式对象
				HSSFRow row = sheet.createRow(0);
				HSSFFont font = wb.createFont();//设置字体
				HSSFCell cell = row.createCell((short) 0);//创建单元格
				Region region = new Region((short) 0, (short) 0, (short) 0,(short) 3);
				sheet.addMergedRegion(region);
				cell.setCellValue("整改商户数据信息"); // 表格的第一行第一列显示的数据
				cell.setCellStyle(top_style); // 样式，居中	
				String[] coloumValue21 = { "序号","银联商户","终端号", "43域", "商户简称",
						"32域","MCC","近三个月是否有交易","直属代理商","一级代理商","所属机构","所属大区","营业执照号","法人名称",
						"省","市","县","商户地址","实际经营地址","商户联系人姓名","商户联系人电话",
						"进件日期","是否冻结","冻结日期"};
				setStatMerColWidth(sheet);
				row = ExcelUtil.createRow(sheet, 1, coloumValue21, data_style, true);//
				List<List<Object>> exportList ;
				if(map.containsKey("data"))
				{
					exportList = (List<List<Object>>) map.get("data");
				}else{
					exportList = new ArrayList<List<Object>>();
				}
				for (int i = 0; i < exportList.size(); i++) {
					List<Object> row_data = exportList.get(i);
					Object[] rowarr = row_data.toArray();
					String[] col_i = new String[rowarr.length];
					for(int j = 0; j< rowarr.length; j++)
					{
						col_i[j] = Constance.Null2EmptyTrim(rowarr[j]);
					}
					row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,true);
					
					}
				
				if(map.containsKey("result"))
				{
					String result = (String) map.get("result");
					String[] coloumValue = { "汇总：", Constance.Null2EmptyTrim(result), "" };
					row = ExcelUtil.createRow(sheet, exportList.size() + 3, coloumValue,
							general_style, true);
				}
			} catch (Exception e) {
				logger.error("########导出信息匹配Excel失败######", e);
				throw new ServiceException("导出信息匹配Excel失败！");
			}
			return wb;
		}

	@Override
	public SXSSFWorkbook exErrorCountList(List<Object> searchRsList)
			throws ServiceException {
		// TODO Auto-generated method stub
		return null;
	}


	@Override
	public HSSFWorkbook exportReviseMerinfoList(List<ReviseMerinfoBean> revList, ReviseMerinfoSearchCondition searchCondition)
			throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes  =  new LinkedHashMap<String, String>();
			respCodes= ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("整改商户数据信息");
			
			 HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
                     false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();//设置字体
			HSSFCell cell = row.createCell((short) 0);//创建单元格
			Region region = new Region((short) 0, (short) 0, (short) 0,(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("整改商户数据信息"+"      "+"导入日期为："+searchCondition.getCreateDate()+"      "+"批次号为："+searchCondition.getBatchNo()); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中	
			String[] coloumValue21 = { "序号","银联商户","终端号", "43域", "商户简称",
					"32域","MCC","近三个月是否有交易","直属代理商","一级代理商","所属机构","所属大区","营业执照号","法人名称",
					"省","市","县","商户地址","实际经营地址","商户联系人姓名","商户联系人电话",
					"进件(审核)日期","是否冻结","冻结日期"};
			setStatMerColWidth(sheet);
			row = ExcelUtil.createRow(sheet, 1, coloumValue21, data_style, true);

			for (int i = 0; i < revList.size(); i++) {
				ReviseMerinfoBean objs = (ReviseMerinfoBean) revList.get(i);

				String[] col_i = {
						objs.getRowNum(),
						objs.getMchtCd(),
						objs.getTermId(),
						objs.getName(),
						objs.getNameBusi(),
						objs.getUcBcCd32(),
						objs.getMccCd18(),
						objs.getIsDeal(),
						objs.getUpAgent(),
						objs.getOneAgent(),
						objs.getOrgNum(),
						objs.getCompanyid(),
						objs.getBusiLiceNo(),
						objs.getCertif(),
						objs.getProvCd(),
						objs.getCityCd(),
						objs.getAreaCd(),
						objs.getRegAddr(),
						objs.getUcBcCd32Zh(),
						objs.getContactName(),
						objs.getContactPhoneno(),
						objs.getApplDate(),
						objs.getStatus(),
						objs.getRecUpdTs(),
//						String.valueOf(objs.getBatchNo()),
					
						};

				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						true);
				
			}

		} catch (Exception e) {
			logger.error("########导出差错明细失败######", e);
			throw new ServiceException("导出差错交易统计失败！");
		}
		return wb;
	}
	
	
	public SXSSFWorkbook exportTempList(
			List<TblSettleItem> settleItemList) throws Exception {
			String title="上海银行对公明细报表";
			SXSSFWorkbook  wb = new SXSSFWorkbook ();
			try {
				SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(title);
				CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
						false); // 首行标题样式对象
				CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
						true); // 数据标题样式对象
				CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
						false); // 普通样式对象
				Row row = sheet.createRow(0);
				Font font = wb.createFont();
				Cell cell = row.createCell((short) 0);
				CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
						(short) 10);
				sheet.addMergedRegion(region);
				cell.setCellValue(title); // 表格的第一行第一列显示的数据
				cell.setCellStyle(top_style); // 样式，居中
				String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
						"", "", "", "", "", "", ""};
				row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
						true);
				String[] coloumValue2={"转账类型","付款单位账号","收款单位账号","收款单位名称","收款单位开户行",
						"收款单位网点名称","收款银行行号","付款金额","付款用途","加急标志"};
					
				row = ExcelUtil.createRow(sheet, 2, coloumValue2, data_style, true);
				
				for (int i = 0; i < settleItemList.size(); i++) {
					TblSettleItem objs = settleItemList.get(i);
					String bankName=objs.getBankName();
					String bankCode=objs.getBankCode();
					String trans="";
					if("325".equals(objs.getBankCode().substring(0,3))){//上海银行
						trans="I";
					}else if("2900".equals(objs.getBankCode().substring(3,7))){//同城跨行
						trans="S";
						Object[] object=applyService.findMainBank(bankName, bankCode);
						if(object!=null){
							bankName=Constance.Null2EmptyTrim(object[0]);
							bankCode=Constance.Null2EmptyTrim(object[1]);
						}
					}else{//异地
						trans="D";
					}
					
					String amt=Constance.Null2EmptyTrim(objs.getUnFreezeAmt());
					if("".equals(amt)){
						amt="0.00";
					}
					String[] col_i = {
						trans,
						"03003190292",
						objs.getAccount(),
						objs.getAccountName(),
						bankName,
						bankName,
						bankCode,
						amt,
						"",
						"N"
						};
					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
				}			
			} catch (Exception e) {
				logger.error("########导出"+title+"Excel明细失败######", e);
				throw new ServiceException("导出"+title+"Excel明细失败！");
			}
			return wb;
	}
/**
 * 商终明细信息导出
 * @see com.roc.service.IExcelService#exportMerinfoTermList(java.util.List, java.lang.String, java.lang.String)
 *
 * @Title: exportMerinfoTermList
 * @Description:TODO(...)
 * @param ccList
 * @param minDate
 * @param maxDate
 * @return
 * @throws ServiceException
 * @author JD
 * @date: 2017年11月15日 下午1:46:44
 *
 */
	@Override
	public HSSFWorkbook exportMerinfoTermList(
			List<MerinfoTermBean> ccList, String minDate, String maxDate) throws ServiceException {
		// TODO Auto-generated method stub
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			HSSFSheet sheet = wb.createSheet("商终明细信息统计");
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 20);
			sheet.addMergedRegion(region);
			cell.setCellValue("商终明细信息统计"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(),
					"商终明细日期范围", minDate, maxDate,"","","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			String[] coloumValue3 = { "商户号", "终端号","终端状态", "商户注册名称",  "商户简称",
					"结算周期","商户营业执照号","商户注册地址","装机地址","法人代表","商户入网时间","商户状态","优惠标准","直属代理商",
							 "所属分公司","所属大区","商户联系人","商户联系手机","MCC编号","MCC大类","省份","城市","发展人","机具产权","终端类型",
							 "终端SN号","申请人","主营业务","终端进件时间","终端关闭时间","T1借记卡费率(%)","T1借记卡封顶值(元)","T1贷记卡费率(%)","T1贷记卡封顶值(元)",
							 "支付宝/微信扫码费率(%)","银联扫码费率(%)","D0状态","D0借记卡费率(%)","D0贷记卡费率(%)","账户性质","入账名称","结算帐号","开户行","特殊计费标志","是否支持预授权"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			
			for (int i = 0; i < ccList.size(); i++) {
				MerinfoTermBean objs = ccList.get(i);
				String accountType="";
				if("0".equals(objs.getApprovalusername())){
					accountType="对私账户";
				}else if("1".equals(objs.getApprovalusername())){
					accountType="对公账户";
				}
				String[] col_i = { 
						Constance.Null2EmptyTrim(objs.getMchtCd()),
						Constance.Null2EmptyTrim(objs.getTermid()),
						Constance.Null2EmptyTrim(objs.getTermStatus()),
						Constance.Null2EmptyTrim(objs.getName()),
						Constance.Null2EmptyTrim(objs.getNameBusi()),
						Constance.Null2EmptyTrim(objs.getAccPeri()),
						Constance.Null2EmptyTrim(objs.getBusiLicnNo()),
						Constance.Null2EmptyTrim(objs.getRegAddr()),
						Constance.Null2EmptyTrim(objs.getInstalllocation()),
						Constance.Null2EmptyTrim(objs.getCertif()),
						Constance.Null2EmptyTrim(objs.getApprDate()),
						Constance.Null2EmptyTrim(objs.getStatus()),
						Constance.Null2EmptyTrim(objs.getDiscNorm()),
						Constance.Null2EmptyTrim(objs.getFilialeName()),
						Constance.Null2EmptyTrim(objs.getCoopOrg()),
						Constance.Null2EmptyTrim(objs.getRegion()),
						Constance.Null2EmptyTrim(objs.getContactName()),
						Constance.Null2EmptyTrim(objs.getContactPhoneno()),
						Constance.Null2EmptyTrim(objs.getMccCd18()),
						Constance.Null2EmptyTrim(objs.getMchtType()),
						Constance.Null2EmptyTrim(objs.getProv()),
						Constance.Null2EmptyTrim(objs.getCity()),
						Constance.Null2EmptyTrim(objs.getExt1()),
						Constance.Null2EmptyTrim(objs.getTermAut()),
						Constance.Null2EmptyTrim(objs.getTermTp()),
						Constance.Null2EmptyTrim(objs.getDeviceCd()),
						Constance.Null2EmptyTrim(objs.getProposer()),
						Constance.Null2EmptyTrim(objs.getBusi_main()),
						Constance.Null2EmptyTrim(objs.getREC_CRT_TS()),
						Constance.Null2EmptyTrim(objs.getTermShutDate()),
						Constance.Null2EmptyTrim(objs.getT1_J()),
						Constance.Null2EmptyTrim(objs.getT1_JF()),
						Constance.Null2EmptyTrim(objs.getT1_D()),
						Constance.Null2EmptyTrim(objs.getT1_DF()),
						Constance.Null2EmptyTrim(objs.getZFB_S()),
						Constance.Null2EmptyTrim(objs.getYL_S()),
						Constance.Null2EmptyTrim(objs.getD0Status()),
						Constance.Null2EmptyTrim(objs.getD0_J()),
						Constance.Null2EmptyTrim(objs.getD0_D()),
						Constance.Null2EmptyTrim(accountType),
						Constance.Null2EmptyTrim(objs.getBackName()),
						Constance.Null2EmptyTrim(objs.getAccount()),
//						Constance.Null2EmptyTrim(objs.getBankNa()),
						Constance.Null2EmptyTrim(objs.getBankName()),
						Constance.Null2EmptyTrim(objs.getExtraRate()),
						Constance.Null2EmptyTrim(objs.getIsYsq())
						};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
			}

		} catch (Exception e) {
			logger.error("########导出商终明细异常######", e);
			throw new ServiceException("导出商终明细异常！");
		}
		return wb;
	
	}
	
	/**
	 * 商户数量统计导出
	 * @author Jc
	 * @date: 2018年1月29
	 *
	 */
	@Override
	public SXSSFWorkbook exportCommNumber(List<CommNumberStatBean> ccList,String MinDate,String MaxDate)
			throws ServiceException {
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("商户数量统计信息");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 4);
			sheet.addMergedRegion(region);
			cell.setCellValue("商户数量统计信息：" + Constance.formateDate()); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
		
			String[] coloumValue = { "时间",	"分公司",	"商户数量",	"累计进件数量",	"所属地区"};
			row = ExcelUtil.createRow(sheet, 1, coloumValue, data_style, true);

			for (int i = 0; i < ccList.size(); i++) {
				CommNumberStatBean objs = ccList.get(i);

				String[] col_i = { MinDate+"-"+MaxDate   ,
						Constance.Null2EmptyTrim(objs.getCompanyName()),
						Constance.Null2EmptyTrim(objs.getCommNumber()),
						Constance.Null2EmptyTrim(objs.getSunPieceNumber()),
						Constance.Null2EmptyTrim(objs.getAreaCode()) };

				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						true);
			}
		} catch (Exception e) {
			logger.error("########商户数量统计信息失败######", e);
			throw new ServiceException("商户数量统计信息失败！");
		}
		return wb;
	}
	
	
	@Override
	public SXSSFWorkbook exportNoEstrnMerchantInfo(List<Object> noEstrnList) throws Exception{
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("连续15天无交易商户详情");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 12);
			sheet.addMergedRegion(region);
			cell.setCellValue("连续15天无交易商户详情-" + Constance.formateDate()); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
		
			String[] coloumValue = { 
					"直属代理",	"合作机构",	"所属大区",	"商户号",	"商户名称",	"终端号", "终端型号",	"终端是否有效" , "绑定时间" , "SN号" ,"装机地址" , 	"创建时间",	"发展人"};
			row = ExcelUtil.createRow(sheet, 1, coloumValue, data_style, true);

			for (int i = 0; i < noEstrnList.size(); i++) {
				NoEstrnMerchantInfo objs = (NoEstrnMerchantInfo) noEstrnList.get(i);

				String[] col_i = { Constance.Null2EmptyTrim(objs.getDirectlyAgent()),
						Constance.Null2EmptyTrim(objs.getOneagentName()),
						Constance.Null2EmptyTrim(objs.getCompanyName()),
						Constance.Null2EmptyTrim(objs.getMchtId()),
						Constance.Null2EmptyTrim(objs.getMchtName()),
						Constance.Null2EmptyTrim(objs.getTermCode()),
						Constance.Null2EmptyTrim(objs.getPosName()),
						Constance.Null2EmptyTrim(objs.getTermStatus()),
						Constance.Null2EmptyTrim(objs.getBrandDate()),
						Constance.Null2EmptyTrim(objs.getSerialNum()),
						Constance.Null2EmptyTrim(objs.getInstalllocation()),
						Constance.Null2EmptyTrim(objs.getCrtDate()),
						Constance.Null2EmptyTrim(objs.getOperatorsName()) };

				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						true);
			}
		} catch (Exception e) {
			logger.error("########导出连续15天无交易商户Excel明细失败######", e);
			throw new ServiceException("导出连续15天无交易商户Excel明细失败！");
		}
		return wb;
	}
	

	@Override
	public SXSSFWorkbook exportWareHouSnStatics(List<Object> noEstrnList) throws Exception{
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("仓库Sn统计详情");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 5);
			sheet.addMergedRegion(region);
			cell.setCellValue("仓库Sn统计详情-" + Constance.formateDate()); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
		
			String[] coloumValue = { 
						"分公司",	"厂商",	"型号",	"总领数",	"总绑定数量",	"当日绑定数量" };
			row = ExcelUtil.createRow(sheet, 1, coloumValue, data_style, true);

			for (int i = 0; i < noEstrnList.size(); i++) {
				WareHouseSNStatisticsInfo objs = (WareHouseSNStatisticsInfo) noEstrnList.get(i);

				String[] col_i = { Constance.Null2EmptyTrim(objs.getCompanyName()),
						Constance.Null2EmptyTrim(objs.getPosBrand()),
						Constance.Null2EmptyTrim(objs.getPosName()),
						Constance.Null2EmptyTrim(objs.getAllCount()),
						Constance.Null2EmptyTrim(objs.getAllBrandCount()),
						Constance.Null2EmptyTrim(objs.getTdBrandCount()) };

				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						true);
			}
		} catch (Exception e) {
			logger.error("########导出仓库Sn统计Excel明细失败######", e);
			throw new ServiceException("导出仓库Sn统计Excel明细失败！");
		}
		return wb;
	}
	

	@Override
	public SXSSFWorkbook exportDayStasticsList(List<DayStastics> staList,
			String minDate, String maxDate, Map<String, String> resMap) throws ServiceException{


		// TODO Auto-generated method stub
		SXSSFWorkbook  wb = new SXSSFWorkbook ();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("日报表");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 15);
			sheet.addMergedRegion(region);
			cell.setCellValue("日报表"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"计费日期", minDate, maxDate, "交易总金额", resMap.get("totalTransAmt"), "手续费总金额", resMap.get("totalFee"), "交易总笔数", resMap.get("totalCount"),"总收入",resMap.get("totalIncome"),""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			
			String[] coloumValue3 = { "计费日期", "商户号", "终端号", "商户名", "省份",
					"城市", "MCC", "MCC大类","合作机构", "所属大区","交易笔数","交易金额", "手续费","收入","发展人" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			

			for (int i = 0; i < staList.size(); i++) {
				DayStastics objs = staList.get(i);


				String[] col_i = { Constance.Null2EmptyTrim(objs.getTransDt()),
						Constance.Null2EmptyTrim(objs.getMchtCd()),
						Constance.Null2EmptyTrim(objs.getTermId()),
						Constance.Null2EmptyTrim(objs.getNameBusi()),
						Constance.Null2EmptyTrim(objs.getProvName()),
						Constance.Null2EmptyTrim(objs.getCityName()),
						Constance.Null2EmptyTrim(objs.getUpMccCd()),
						Constance.Null2EmptyTrim(objs.getMccSuperName()),
						Constance.Null2EmptyTrim(objs.getOrgName()),
						Constance.Null2EmptyTrim(objs.getCompanyName()),
						Constance.Null2EmptyTrim(objs.getTotalCount()),
						Constance.Null2EmptyTrim(objs.getTotalAmt()),
						Constance.Null2EmptyTrim(objs.getMchtFee()),
						Constance.Null2EmptyTrim(objs.getInCome()),
						Constance.Null2EmptyTrim(objs.getRealName())};

				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
						true);
			}

		} catch (Exception e) {
			logger.error("########导出日报表失败######", e);
			throw new ServiceException("导出日报表失败！");
		}
		return wb;

	
	}


	/**
	 * 导出分公司交易统计	
	 * @Title: exportCompTransList
	 * @Description:TODO(...)
	 * @see <p></p>
	 * @param staList
	 * @param minDate
	 * @param maxDate
	 * @return
	 * @author JD
	 * @date: 2017年12月4日 下午3:34:58
	 *
	 */
	@Override
	public SXSSFWorkbook exportCompTransList(List<CompTransCount> staList,
			String minDate, String maxDate)  throws ServiceException{
		// TODO Auto-generated method stub
				SXSSFWorkbook  wb = new SXSSFWorkbook ();
				try {
					Map<String, String> respCodes = ConfigProperties
							.getRespCodeQueryTypes();
					SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("分公司交易统计");
					CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
							false); // 首行标题样式对象
					CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
							true); // 数据标题样式对象
					CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
							false); // 普通样式对象
					Row row = sheet.createRow(0);
					Font font = wb.createFont();
					Cell cell = row.createCell((short) 0);
					CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
							(short) 8);
					sheet.addMergedRegion(region);
					cell.setCellValue("分公司交易统计"); // 表格的第一行第一列显示的数据
					cell.setCellStyle(top_style); // 样式，居中
					String dateStr = minDate+"-"+maxDate;
					String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
							"统计日期", minDate, maxDate, "", ""};
					row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
							true);

					
					String[] coloumValue3 = { "统计时间", "分公司", "总金额", "总笔数", "区域",
							"活跃商户数量", "手续费", "收入" };
					row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
					

					for (int i = 0; i < staList.size(); i++) {
						CompTransCount objs = staList.get(i);


						String[] col_i = { 
								Constance.Null2EmptyTrim(objs.getTransDt()),
								Constance.Null2EmptyTrim(objs.getOrgName()),
								Constance.Null2EmptyTrim(objs.getSumAmt()),
								Constance.Null2EmptyTrim(objs.getSumCount()),
								Constance.Null2EmptyTrim(objs.getCompanyName()),
								Constance.Null2EmptyTrim(objs.getActiveMchtCount()),
								Constance.Null2EmptyTrim(objs.getMchtFee()),
								Constance.Null2EmptyTrim(objs.getInCome())};
							
						row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
								true);
					}

				} catch (Exception e) {
					logger.error("########导出日报表失败######", e);
					throw new ServiceException("导出日报表失败！");
				}
				return wb;

			
			}

	@Override
	public SXSSFWorkbook exportOrgApplyMoneyList(
			List<OrgSettlementBean> settleItemList) throws Exception {
			String title="机构出款信息";
			SXSSFWorkbook  wb = new SXSSFWorkbook ();
			try {
				SXSSFSheet sheet = (SXSSFSheet) wb.createSheet(title);
				CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
						false); // 首行标题样式对象
				CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
						true); // 数据标题样式对象
				CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
						false); // 普通样式对象
				Row row = sheet.createRow(0);
				Font font = wb.createFont();
				Cell cell = row.createCell((short) 0);
				CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
						(short) 14);
				sheet.addMergedRegion(region);
				cell.setCellValue(title); // 表格的第一行第一列显示的数据
				cell.setCellStyle(top_style); // 样式，居中
				String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
						"", "", "", "", "", "", "", "","","","",""};
				row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
						true);
				String[] coloumValue2={"交易键值","核心机构流水号","出款状态","出款金额（元）","总笔数（笔）","费率",
						"入账卡号","银行名称","入账姓名","机构号","账号","手机号","出款类型","流水号","结算日期","交易日期"};
					
				row = ExcelUtil.createRow(sheet, 2, coloumValue2, data_style, true);
				
				for (int i = 0; i < settleItemList.size(); i++) {
					OrgSettlementBean objs = settleItemList.get(i);
					
					String[] col_i = {
						objs.getKeyRsp(),
						objs.getTranSeq(),
						objs.getStatus(),
						String.valueOf(objs.getAmt()),
						String.valueOf(objs.getTotalNum()),
						String.valueOf(objs.getFee()),
						objs.getAcctNo(),
						objs.getBankName(),
						objs.getAcctName(),
						objs.getOrgCode(),
						objs.getAccountNo(),
						objs.getMobileNo(),
						objs.getTransCd(),					
						objs.getRemark1(),
						objs.getSettleDate(),
						objs.getRemark6()
						};
					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
				}			
			} catch (Exception e) {
				logger.error("########导出机构出款"+title+"Excel明细失败######", e);
				throw new ServiceException("导出机构出款"+title+"Excel明细失败！");
			}
			return wb;
	}

	@Override
	public SXSSFWorkbook exportYsbHisTransFlow(List<TblTfrHisTrnLog> tftList,
			String minDate, String maxDate, Map<String, String> resMap,String roleType)
			throws Exception {


		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("历史交易明细");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 15);
			sheet.addMergedRegion(region);
			cell.setCellValue("历史交易明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			/*String[] coloumValue1 = { "报表日期：", Constance.formateDate(), "",
					"交易日期范围", minDate, maxDate, "", "交易成功总金额", resMap.get("totalAmt"), "", "交易成功总笔数", resMap.get("totalCount"),"","",""};*/
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(),
					"交易日期范围", minDate, maxDate,"交易成功总金额", resMap.get("totalAmt"),"交易成功总笔数", resMap.get("totalCount"), "撤销成功总金额", resMap.get("cxTotalAmt"),"撤销成功总笔数",resMap.get("cxTotalCount"),"","","","","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			/*String[] coloumValue3 = { "交易日期", "交易时间", "交易类型", "交易结果", "是否被处理","交易账号",
					"卡类型", "交易金额", "商户名", "商户号", "终端号", "流水号", "系统参考号" };*/
			String[] coloumValue3 = null;
			if(Role.AGENT.getCode().equals(roleType)){
				coloumValue3 = new String[]{ "商户号", "商户名", "终端号","SN", "交易账号", "交易金额(元)",
						"交易类型","银联返回码", "交易结果", "交易状态","交易日期", "交易时间", "卡类型","接触方式", "流水号", "系统参考号","系统跟踪号","交易参考号" };
			}else{
				coloumValue3 = new String[]{ "商户号", "商户名", "终端号","SN", "交易账号", "交易金额(元)",
						"交易类型","银联返回码", "交易结果", "交易状态","交易日期", "交易时间", "卡类型","接触方式", "流水号", "系统参考号","系统跟踪号","交易参考号","银联商户号" };
			}
			
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			if(Role.AGENT.getCode().equals(roleType)){
				for (int i = 0; i < tftList.size(); i++) {
					TblTfrHisTrnLog objs = tftList.get(i);

					String[] col_i = { Constance.Null2EmptyTrim(objs.getMchntCd()),
							Constance.Null2EmptyTrim(objs.getMchntNmConv()),
							Constance.Null2EmptyTrim(objs.getTermId()),
							Constance.Null2EmptyTrim(objs.getPmtNo()),
							Constance.Null2EmptyTrim(objs.getPriAcctNo()),
							Constance.Null2EmptyTrim(objs.getTransAt()),
							Constance.Null2EmptyTrim(objs.getTransInf()),
							Constance.Null2EmptyTrim(objs.getRespCd()),
							Constance.Null2EmptyTrim(objs.getRespName()),
							Constance.Null2EmptyTrim(objs.getDealRs()),
							Constance.Null2EmptyTrim(objs.getTransDt()),
							Constance.Null2EmptyTrim(objs.getTransMt()),
							Constance.Null2EmptyTrim(objs.getCardType()),
							Constance.Null2EmptyTrim(objs.getPosEntryMdCdName()),
							Constance.Null2EmptyTrim(objs.getMaTransSeq()),
							Constance.Null2EmptyTrim(objs.getKeyRsp()),
							Constance.Null2EmptyTrim(objs.getRetriRefNo()),
							Constance.Null2EmptyTrim(objs.getRetriRefNoConv())	};

					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
					
				}
			}else{
				for (int i = 0; i < tftList.size(); i++) {
					TblTfrHisTrnLog objs = tftList.get(i);

					String[] col_i = { Constance.Null2EmptyTrim(objs.getMchntCd()),
							Constance.Null2EmptyTrim(objs.getMchntNmConv()),
							Constance.Null2EmptyTrim(objs.getTermId()),
							Constance.Null2EmptyTrim(objs.getPmtNo()),
							Constance.Null2EmptyTrim(objs.getPriAcctNo()),
							Constance.Null2EmptyTrim(objs.getTransAt()),
							Constance.Null2EmptyTrim(objs.getTransInf()),
							Constance.Null2EmptyTrim(objs.getRespCd()),
							Constance.Null2EmptyTrim(objs.getRespName()),
							Constance.Null2EmptyTrim(objs.getDealRs()),
							Constance.Null2EmptyTrim(objs.getTransDt()),
							Constance.Null2EmptyTrim(objs.getTransMt()),
							Constance.Null2EmptyTrim(objs.getCardType()),
							Constance.Null2EmptyTrim(objs.getPosEntryMdCdName()),
							Constance.Null2EmptyTrim(objs.getMaTransSeq()),
							Constance.Null2EmptyTrim(objs.getKeyRsp()),
							Constance.Null2EmptyTrim(objs.getRetriRefNo()),
							Constance.Null2EmptyTrim(objs.getRetriRefNoConv()),
							Constance.Null2EmptyTrim(objs.getMchntCdConv())};

					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
				}
			}
			

		} catch (Exception e) {
			logger.error("########导出云商宝交易Excel明细失败######", e);
			throw new ServiceException("导出云商宝历史交易Excel明细失败！");
		}
		return wb;
	
	}

	@Override
	public SXSSFWorkbook exportYsbTransFlow(List<TblTfrTrnLog> tftList,
			String minDate, String maxDate, Map<String, String> resMap,String roleType)
			throws ServiceException {

		// TODO Auto-generated method stub
		SXSSFWorkbook  wb = new SXSSFWorkbook ();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("交易明细");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 15);
			sheet.addMergedRegion(region);
			cell.setCellValue("交易明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(),
					"交易日期", minDate, maxDate,"交易成功总金额", resMap.get("totalAmt"),"交易成功总笔数", resMap.get("totalCount"), "撤销成功总金额", resMap.get("cxTotalAmt"),"撤销成功总笔数",resMap.get("cxTotalCount"),"","","",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			/*String[] coloumValue3 = { "交易日期", "交易时间", "交易类型", "交易结果", "是否被处理","交易账号",
					"卡类型", "交易金额", "商户名", "商户号", "终端号", "流水号", "系统参考号" };*/
			String[] coloumValue3 = null;
			if(Role.AGENT.getCode().equals(roleType)){
				coloumValue3 = new String[]{ "商户号", "商户名", "终端号","SN", "交易账号", "交易金额(元)",
						"交易类型", "银联返回码", "交易结果", "交易状态","交易日期", "交易时间", "卡类型","接触方式", "流水号", "系统参考号","系统跟踪号","交易参考号" };
			}else{
				coloumValue3 = new String[]{ "商户号", "商户名", "终端号","SN", "交易账号", "交易金额(元)",
						"交易类型", "银联返回码", "交易结果", "交易状态","交易日期", "交易时间", "卡类型","接触方式", "流水号", "系统参考号","系统跟踪号","交易参考号","银联商户号" };
			}
			
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			// Double amt=0.0;//总金额
			// Double feeAmt=0.0;//总手续费

			if(Role.AGENT.getCode().equals(roleType)){
				for (int i = 0; i < tftList.size(); i++) {
					TblTfrTrnLog objs = tftList.get(i);

					String[] col_i = { Constance.Null2EmptyTrim(objs.getMchntCd()),
							Constance.Null2EmptyTrim(objs.getMchntNmConv()),
							Constance.Null2EmptyTrim(objs.getTermId()),
							Constance.Null2EmptyTrim(objs.getPmtNo()),
							Constance.Null2EmptyTrim(objs.getPriAcctNo()),
							Constance.Null2EmptyTrim(objs.getTransAt()),
							Constance.Null2EmptyTrim(objs.getTransInf()),
							Constance.Null2EmptyTrim(objs.getRespCd()),
							Constance.Null2EmptyTrim(objs.getRespName()),
							Constance.Null2EmptyTrim(objs.getDealRs()),
							Constance.Null2EmptyTrim(objs.getTransDt()),
							Constance.Null2EmptyTrim(objs.getTransMt()),
							Constance.Null2EmptyTrim(objs.getCardType()),
							Constance.Null2EmptyTrim(objs.getPosEntryMdCdName()),
							Constance.Null2EmptyTrim(objs.getMaTransSeq()),
							Constance.Null2EmptyTrim(objs.getKeyRsp()),
							Constance.Null2EmptyTrim(objs.getRetriRefNo()),
							Constance.Null2EmptyTrim(objs.getRetriRefNoConv())
							};

					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
				}
			}else{
				for (int i = 0; i < tftList.size(); i++) {
					TblTfrTrnLog objs = tftList.get(i);

					String[] col_i = { Constance.Null2EmptyTrim(objs.getMchntCd()),
							Constance.Null2EmptyTrim(objs.getMchntNmConv()),
							Constance.Null2EmptyTrim(objs.getTermId()),
							Constance.Null2EmptyTrim(objs.getPmtNo()),
							Constance.Null2EmptyTrim(objs.getPriAcctNo()),
							Constance.Null2EmptyTrim(objs.getTransAt()),
							Constance.Null2EmptyTrim(objs.getTransInf()),
							Constance.Null2EmptyTrim(objs.getRespCd()),
							Constance.Null2EmptyTrim(objs.getRespName()),
							Constance.Null2EmptyTrim(objs.getDealRs()),
							Constance.Null2EmptyTrim(objs.getTransDt()),
							Constance.Null2EmptyTrim(objs.getTransMt()),
							Constance.Null2EmptyTrim(objs.getCardType()),
							Constance.Null2EmptyTrim(objs.getPosEntryMdCdName()),
							Constance.Null2EmptyTrim(objs.getMaTransSeq()),
							Constance.Null2EmptyTrim(objs.getKeyRsp()),
							Constance.Null2EmptyTrim(objs.getRetriRefNo()),
							Constance.Null2EmptyTrim(objs.getRetriRefNoConv()),
							Constance.Null2EmptyTrim(objs.getMchntCdConv())
							};

					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
				}
			}
			

		} catch (Exception e) {
			logger.error("########导出交易Excel明细失败######", e);
			throw new ServiceException("导出交易Excel明细失败！");
		}
		return wb;

	}

	@Override
	public SXSSFWorkbook exportYsbMerDetaiList(List<MerDetailForysBean> merinfoList, String minDate, String maxDate) throws Exception {
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("付临门云商宝会员实名信息");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 17, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 5);
			sheet.addMergedRegion(region);
			cell.setCellValue("付临门云商宝会员实名信息"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate()};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,true);
			String[] coloumValue3 = { "会员手机号", "提交时间", "领件时间", "审核时间", "操作员", "审核状态" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			for (int i = 0; i < merinfoList.size(); i++) {
				MerDetailForysBean objs = merinfoList.get(i);
				String[] col_i = { 
						Constance.Null2EmptyTrim(Utils.dealMobile(objs.getMobile())),
						Constance.Null2EmptyTrim(objs.getCreateDate()),
						Constance.Null2EmptyTrim(objs.getReceiveDate()),
						Constance.Null2EmptyTrim(objs.getAuditDate()),
						Constance.Null2EmptyTrim(objs.getOprNo()),
						Constance.Null2EmptyTrim(objs.getStatus()),
				};
				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,true);
			}
		} catch (Exception e) {
			logger.error("########导出付临门云商宝会员信息Excel失败######", e);
			throw new ServiceException("导出付临门云商宝会员信息Excel失败！");
		}
		return wb;
	}

	@Override
	public SXSSFWorkbook exportYsbTermActList(List<TermActForYsbBean> termActForYsbList) throws Exception {
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("付临门云商宝终端激活数量统计");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 17, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 5);
			sheet.addMergedRegion(region);
			cell.setCellValue("付临门云商宝终端激活数量统计"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表日期：", Constance.formateDate()};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,true);
			String[] coloumValue3 = { "大区", "一级代理商名称", "一级代理商账号", "数量", "前一日数量","入网方式"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			for (int i = 0; i < termActForYsbList.size(); i++) {
				TermActForYsbBean objs = termActForYsbList.get(i);
				String[] col_i = { 
						Constance.Null2EmptyTrim(objs.getAreaName()),
						Constance.Null2EmptyTrim(objs.getOneagentName()),
						Constance.Null2EmptyTrim(objs.getOneagentNo()),
						Constance.Null2EmptyTrim(objs.getPosCount()),
						Constance.Null2EmptyTrim(objs.getYesday()),
						Constance.Null2EmptyTrim(objs.getPosType()),
				};
				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,true);
			}
		} catch (Exception e) {
			logger.error("########导出付临门云商宝终端激活数量统计Excel失败######", e);
			throw new ServiceException("导出付临门云商宝终端激活数量统计Excel失败！");
		}
		return wb;
	}
	
	
	
	@Override
	public HSSFWorkbook exportMerchantsChangeRecordList(List<MerchantsChangeRecordBean> mcrList,
			MerchantsChangeRecordSearchCondition searchCondition)throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes  =  new LinkedHashMap<String, String>();
			respCodes= ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet("云商宝商户变更记录");
			
			 HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
                     false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();//设置字体
			HSSFCell cell = row.createCell((short) 0);//创建单元格
			Region region = new Region((short) 0, (short) 0, (short) 0,(short) 10);
			sheet.addMergedRegion(region);
			cell.setCellValue("云商宝商户变更记录"+"      "+"导出日期为："+new SimpleDateFormat("yyyy-MM-dd").format(new Date())); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中	
			String[] coloumValue10 = { "商户号","姓名", "手机号", "原账户账号",
					"原银行名称","原支行ID","新账户账号","新银行名称","新支行ID","变更时间"};
			setStatMerColWidth(sheet);
			row = ExcelUtil.createRow(sheet, 1, coloumValue10, data_style, true);

			for (int i = 0; i < mcrList.size(); i++) {
				MerchantsChangeRecordBean objs = (MerchantsChangeRecordBean) mcrList.get(i);

				String[] col_i = {
						objs.getMchtCd(),
						objs.getNameOld(),
						objs.getPhoneNo(),
						objs.getAccountNoOld(),
						objs.getBankNameOld(),
						objs.getBankIdOld(),
						objs.getAccountNoNew(),
						objs.getBankNameNew(),
						objs.getBankIdNew(),
						objs.getCreateat()
						};

				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						true);
				
			}

		} catch (Exception e) {
			logger.error("########导出商户变更记录失败######", e);
			throw new ServiceException("导出商户变更记录失败！");
		}
		return wb;
	}

	@Override
	public SXSSFWorkbook exportTransSumList(List<TblActSum> transSumList,
			String minDate, String maxDate) throws Exception{


		// TODO Auto-generated method stub
		SXSSFWorkbook  wb = new SXSSFWorkbook ();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("云商宝商户活动交易统计");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 11);
			sheet.addMergedRegion(region);
			cell.setCellValue("云商宝商户活动交易统计"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate(),
					"交易日期", minDate, maxDate,"",""};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);

			
			String[] coloumValue3 =  new String[]{"商户号","会员手机号码","商户简称","身份证号码","SN","终端绑定日期","终端类型"
					,"结算账号","活动日期1","活动日期2","累计金额","活动达标","达标日期","达标金额"};
			
			
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);

				for (int i = 0; i < transSumList.size(); i++) {
					TblActSum objs = transSumList.get(i);

					String[] col_i = { Constance.Null2EmptyTrim(objs.getMchtCd()),
							Constance.Null2EmptyTrim(objs.getPhNo()),
							Constance.Null2EmptyTrim(objs.getNameBusi()),
							Constance.Null2EmptyTrim(objs.getCertifNo()),
							Constance.Null2EmptyTrim(objs.getSerialNum()),
							Constance.Null2EmptyTrim(objs.getBindDate()),
							Constance.Null2EmptyTrim(objs.getAssetFlag()),
							Constance.Null2EmptyTrim(objs.getAccount()),
							Constance.Null2EmptyTrim(objs.getRemark1()),
							Constance.Null2EmptyTrim(objs.getRemark2()),
							Constance.Null2EmptyTrim(objs.getTransAt()),
							Constance.Null2EmptyTrim(objs.getIsSuccess()),
							Constance.Null2EmptyTrim(objs.getDesDate()),
							Constance.Null2EmptyTrim(objs.getDesAt()),
							};

					row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,
							true);
				}
			
			

		} catch (Exception e) {
			logger.error("########导出云商宝交易活动统计失败######", e);
			throw new ServiceException("导出云商宝交易活动统计失败！");
		}
		return wb;

	
	}
	

	@Override
	public HSSFWorkbook excelTerminalQuery(List<TermBean> termList) throws ServiceException {
		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			HSSFSheet sheet = wb.createSheet( "终端信息导出" );
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb, 11, false,
					true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,
					(short) 9 );// [(short) 0,(short) 9] 合并 0-9 这几列单元格
			sheet.addMergedRegion(region);
			cell.setCellValue( "终端信息导出" ); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中

			String [] coloumValue3 = { "商户号" ,"商户名称" ,"所属机构 " ,"所属代理商" ,"终端号" ,"终端序列号" ,"门店名称" ,"终端地址" ,"终端类型" ,"终端状态"};

			row = ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, true);
			
			for (int i = 0; i < termList.size(); i++) {
				TermBean objs = termList.get(i);
				String[] col_i = { Constance.Null2EmptyTrim(objs.getMid()),
						Constance.Null2EmptyTrim(objs.getMerName()),
						Constance.Null2EmptyTrim(objs.getOrgName()),
						Constance.Null2EmptyTrim(objs.getAgentName()),
						Constance.Null2EmptyTrim(objs.getTid()),
						Constance.Null2EmptyTrim(objs.getSerialNum()),
						Constance.Null2EmptyTrim(objs.getTermName()),
						Constance.Null2EmptyTrim(objs.getTermAddress()),
						Constance.Null2EmptyTrim(objs.getTidKind()),
						Constance.Null2EmptyTrim(objs.getFlag()) };
				
				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						true);
			}
	

		} catch (Exception e) {
			logger.error("########终端信息导出Excel明细失败######", e);
			throw new ServiceException("终端信息导出Excel明细失败！");
		}
		return wb;
	}

	@Override
	public SXSSFWorkbook exportRiskYsbHisTransFlow(List<RiskYsbEstrnInfoBean> tftList, String minDate, String maxDate,
			String roleType) throws ServiceException {
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("云商宝交易信息查询-风控角色");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 17, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 11);
			sheet.addMergedRegion(region);
			cell.setCellValue("云商宝交易信息查询-风控角色"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String dateStr = minDate+"-"+maxDate;
			String[] coloumValue1 = { "报表日期：", Constance.formateDate()};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);
			String[] coloumValue3 = { "注册手机号", "银联商户号", "银联终端号", "交易卡号", "接触方式", "交易日期", "交易时间", "交易流水号", "银联交易参考号", "交易返回信息", "交易地址(终端经纬度)" };
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			for (int i = 0; i < tftList.size(); i++) {
				RiskYsbEstrnInfoBean objs = tftList.get(i);
			               	 
				String[] col_i = { 
						Constance.Null2EmptyTrim(objs.getMobileNo()),
						Constance.Null2EmptyTrim(objs.getMchntCdConv()),
						Constance.Null2EmptyTrim(objs.getUnTermCd()),
						Constance.Null2EmptyTrim(objs.getPriAcctNo()),
						Constance.Null2EmptyTrim(objs.getPosEntryMdCdName()),
						Constance.Null2EmptyTrim(objs.getTransDt()),
						Constance.Null2EmptyTrim(objs.getTransMt()),
						Constance.Null2EmptyTrim(objs.getMaTransSeq()),
						Constance.Null2EmptyTrim(objs.getRetriRefNoConv()),
						Constance.Null2EmptyTrim(objs.getRespName()),
						Constance.Null2EmptyTrim(objs.getCertifId())
						
				};
				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,true);
			}

		} catch (Exception e) {
			logger.error("########导出黑名单Excel失败######", e);
			throw new ServiceException("导出黑名单Excel失败！");
		}
		return wb;
	}
     /**
      * 导出退货保证金
      * @see com.roc.service.IExcelService#exportMarginAndReturnDetails(java.util.List)
      *
      * @Title: exportMarginAndReturnDetails
      * @Description:TODO(...)
      * @param riskList
      * @return
      * @throws ServiceException
      * @author JD
      * @date: 2018年4月24日 下午5:38:41
      *
      */
	@Override
	public SXSSFWorkbook exportMarginAndReturnDetails(List<TblMgmRiskBackLog> riskList) throws ServiceException {
		SXSSFWorkbook wb = new SXSSFWorkbook();
		try {
			Map<String, String> respCodes = ConfigProperties
					.getRespCodeQueryTypes();
			SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("退货保证金明细");
			CellStyle top_style = ExcelUtil.initCellStyle(wb, 17, false,
					false); // 首行标题样式对象
			CellStyle data_style = ExcelUtil.initCellStyle(wb, 17, false,
					true); // 数据标题样式对象
			CellStyle general_style = ExcelUtil.initCellStyle(wb, 0, true,
					false); // 普通样式对象
			Row row = sheet.createRow(0);
			Font font = wb.createFont();
			Cell cell = row.createCell((short) 0);
			CellRangeAddress region = new CellRangeAddress((short) 0, (short) 0, (short) 0,
					(short) 14);
			sheet.addMergedRegion(region);
			cell.setCellValue("退货保证金明细"); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中
			String[] coloumValue1 = { "报表日期：", Constance.formateDate()};
			row = ExcelUtil.createRow(sheet, 1, coloumValue1, general_style,
					true);
			String[] coloumValue3 = { "日期", "时间", "交易键值", "机构号", "商户号", "商户名","终端号" ,"保证金金额","变动前剩余金额",
					"退货金额","变动后剩余额度","操作标识","操作人","备注","状态"};
			row = ExcelUtil.createRow(sheet, 2, coloumValue3, data_style, true);
			for (int i = 0; i < riskList.size(); i++) {
				TblMgmRiskBackLog objs = riskList.get(i);
				  String oprType ="";
                  if("01".equals(objs.getOprType())) {
                	  oprType="保证金变动";
                  }else if("02".equals(objs.getOprType())) {
                	  oprType="退货额度变动";
                  }   
                  String status ="";
                  if("00".equals(objs.getStatus())){
                	  status="交易完成";
                  }else if("01".equals(objs.getStatus())){
                	  status="退货中";
                  }else if("03".equals(objs.getStatus())) {
                	  status="退货失败";
                  }
				  
				String[] col_i = { 
						Constance.Null2EmptyTrim(objs.getTransMt()),
						Constance.Null2EmptyTrim(objs.getTransDt()),
						Constance.Null2EmptyTrim(objs.getKeyRsp()),
						Constance.Null2EmptyTrim(objs.getInsIdCd()),
						Constance.Null2EmptyTrim(objs.getMchtCd()),
						Constance.Null2EmptyTrim(objs.getName()),
						Constance.Null2EmptyTrim(objs.getTermId()),
						Constance.Null2EmptyTrim(objs.getSecurityAmt()),
						Constance.Null2EmptyTrim(objs.getPreAmt()),
						Constance.Null2EmptyTrim(objs.getTranAmt()),
						Constance.Null2EmptyTrim(objs.getCurrentAmt()),
						Constance.Null2EmptyTrim(oprType),
						Constance.Null2EmptyTrim(objs.getAccountNo()),
						Constance.Null2EmptyTrim(objs.getRemark()),
						Constance.Null2EmptyTrim(status),
								
						
				};
				row = ExcelUtil.createRow(sheet, 3 + i, col_i, general_style,true);
			}

		} catch (Exception e) {
			logger.error("########导出退货保证金明细Excel失败######", e);
			throw new ServiceException("导出退货保证金明细Excel失败！");
		}
		return wb;
	}
}

